CODEWITHSUNDEEP
sqlsql-servernull
Nathan
Nathan

Reputation: 25006

sql combine two columns that might have null values

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?

Upvotes: 2

Views: 13450

Answers (3)

RB.
RB.

Reputation: 37182

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.

Upvotes: 2

CloudyMarble
CloudyMarble

Reputation: 37566

Use TRIM() arount the ISNULL() function, or LTRIM() around the entire selected term

Upvotes: 0

Pranav
Pranav

Reputation: 8871

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

SEE LTRIM()

Upvotes: 3

Related Questions