sql combine two columns that might have null values

12,867

Solution 1

select 'All'= LTRIM(ISNULL(Name+' ','')+ISNULL(City+' ','')+ISNULL(CAST(Age as varchar(50))+' ','')  from zPerson)

SEE LTRIM()

Solution 2

In the data you have posted, the Name column contains no NULLs. Instead, it contains empty strings, so ISNULL(Name+' ','') will evalate to a single space.

The simplest resolution is to change the data so that empty-strings are null. This is appropriate in your case since this is clearly your intention.

UPDATE zPerson SET Name=NULL WHERE Name=''

Repeat this for your City and Age fields if necessary.

Share:
12,867
Nathan
Author by

Nathan

Updated on July 30, 2022

Comments

  • Nathan
    Nathan over 1 year

    This should be an easy thing to do but I seem to keep getting an extra space. Basically what I am trying to do is combine multiple columns into one column. BUT every single one of these columns might be null as well. When I combine them, I also want them to be separated by a space (' ').

    What I created is the following query:

    select 'All'= ISNULL(Name+' ','')+ISNULL(City+' ','')+ISNULL(CAST(Age as varchar(50))+' ','')  from zPerson
    

    and the result is:

    All
    John Rock Hill 23 
     Munchen 29 
    Julie London 35 
     Fort Mill 27 
    Bob  29 
    

    As you can see: there is an extra space when the name is null. I don't want that.

    The initial table is :

    id  Name    City            Age InStates    AllCombined
    1   John    Rock Hill       23  1              NULL
    2           Munchen         29  0              NULL
    3   Julie   London          35  0              NULL
    4           Fort Mill       27  1              NULL
    5   Bob                     29  1              NULL
    

    Any ideas?

  • Maurício
    Maurício almost 11 years
    That would be no good for him. TRIM(ISNULL(Name + ' ', '')) would remove the spaces he had so carefully added!
  • Maurício
    Maurício almost 11 years
    That doesn't help him for the final row, which gives Bob__27 (with 2 spaces)
  • Nathan
    Nathan almost 11 years
    +1 for a solution that works around the problem and it's done in 2 steps. This would be a good solution but my tables haveover 1 million records...but Pranav's solution is right on the spot
  • Nathan
    Nathan almost 11 years
    RB you are correct.. but his solution gave me the idea to use ltrim for every single field.. so his solution is the one that solved the problem: the final query that i used is : select 'All'= ltrim(isnull(Name+' ',''))+ltrim(isnull(City+' ',''))+ltrim(isnull(CAST(Age as varchar(50))+' ','')) from zPerson
  • Pranav
    Pranav almost 11 years
    @RB Yes you are right.. but OP only wants to remove first extra spaces that's i suggested this to him..
  • Maurício
    Maurício almost 11 years
    Out of interest, why does the fact that you have a million records prevent this being a good solution? Also note that this is not a workaround - it is the correct answer! If you do not know someone's name you should store it as NULL, as otherwise you are claiming that their name is an empty-string - databases are all about semantics :)
  • Nathan
    Nathan almost 11 years
    the data comes daily from different sources some as null, some as empty strings... We are talking about millions of records daily with over 40 fields each, so im sorry for not giving more details...
  • Maurício
    Maurício almost 11 years
    Well, if you've decided it's not the right approach for pragmatic reasons, that's fair enough :)
  • Maurício
    Maurício almost 11 years
    @Pranav Re-reading the question I agree you're right - you did answer the OPs query, so +1 :)