T-sql – INSERT INTO FROM: Concatenating Forename and Surname when Forename can be blank,

azure-sql-databaset-sql

I have a staging (RAW) table that contains one field for to hold a person's first name, and another for a person's last name. I want to insert those value on a production (DBO) table, where the person's forename and surname will be concatenated:

INSERT INTO [dbo].Test
SELECT firstname + ' ' + lastname
      ,field1
      ,field2
      ,fieldn
FROM RAW.Test

Problem however is that I just found out that the firstname field can be NULL (no blanks, but NULL) in the RAW table. Considering the code I have above, this would mean that the person's name would be " Smith" (note the trailing space) instead of "Smith".

What would be the best way to check for such issue and only add the trailing space between firstname and lastname if firstname contains a value?

Note that firstname would never be blank, since the RAW table has a NULLIF conditional that replaces all blanks with NULL.

Thanks for your help and support!

P.

EDIT: Sorry for the lame edit but I made a mistake, the firsname field is the one that can be blank (which makes sense if you see the example I gave about " Smith").

Best Answer

INSERT INTO [dbo].Test
select firstname +  ISNULL(' ' + lastname,'')
    ,field1
    ,field2
    ,fieldn
FROM RAW.Test

Try this. It will concatenate empty string to firstname in case of NULL in lastname

In case we are talking about Firstname that can be null your query should be:

INSERT INTO [dbo].Test
select ISNULL(firstname + ' ','') + lastname
    ,field1
    ,field2
    ,fieldn
FROM RAW.Test
Related Question