0

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").

1
  • COALESCE(lastname,'')
    – dnoeth
    Aug 25 '16 at 14:20
2
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
4
  • 1
    And the shortcut is COALESCE(' ' + lastname,'')
    – dnoeth
    Aug 25 '16 at 14:21
  • I made a serious mistake in my text. The field that can be NULL is the firstname field, not the lastname field :( As a result none of those codes seem to work since the first part of the string being null seems to be an issue.
    – pmdci
    Aug 25 '16 at 14:39
  • 1
    easier: INSERT INTO [dbo].Test select ISNULL(firstname + ' ','')+ lastname ,field1 ,field2 ,fieldn FROM RAW.Test Aug 25 '16 at 14:41
  • Thanks Liya! Your code is actually quite elegant and makes total sense. I was going through hoops and never thought that "hey, I can pass "firstname + <space>" to ISNULL! :) -- Thanks again. You should edit the response in case someone looks for the same question as me, though.
    – pmdci
    Aug 25 '16 at 14:49
3

Liya has a nice solution. This is another that handles both the null case and the empty string last name case as well.

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

EDIT: Modified to allow either field to be nullable or an empty string with trimming in full effect in response to the modified requirements of the OP.

6
  • ISNULL(LastName,'') is a good alternative as well, as COALESCE is more for multiple columns/values where ISNULL assesses one column/value.
    – SQLDevDBA
    Aug 25 '16 at 14:28
  • Sorry I had a semicolon where a comma should have been. Please try again.
    – BZN_DBer
    Aug 25 '16 at 14:33
  • 1
    Actually it was my fault. I said that lastname can be null, which was incorrect (see the example if " Smith"). The firstname is the field that can be null. sorry for the lame-*ss mistake.
    – pmdci
    Aug 25 '16 at 14:37
  • That too. I'm a mess today. Thanks for the edit love @ypercube
    – BZN_DBer
    Aug 25 '16 at 14:37
  • Thanks a lot for your version of the code as well BZN_DBer. In fact your seems to handle both blank and nulls as you said. Even though blanks don't apply in my case, your code seems to be more complete -- although I do not understand coalesce well enough yet.
    – pmdci
    Aug 26 '16 at 12:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.