sql combine two columns that might have null values
Solution 1
select 'All'= LTRIM(ISNULL(Name+' ','')+ISNULL(City+' ','')+ISNULL(CAST(Age as varchar(50))+' ','') from zPerson)
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.
Nathan
Updated on July 30, 2022Comments
-
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 almost 11 yearsThat would be no good for him.
TRIM(ISNULL(Name + ' ', ''))
would remove the spaces he had so carefully added! -
Maurício almost 11 yearsThat doesn't help him for the final row, which gives
Bob__27
(with 2 spaces) -
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 almost 11 yearsRB 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 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 almost 11 yearsOut 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 almost 11 yearsthe 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 almost 11 yearsWell, if you've decided it's not the right approach for pragmatic reasons, that's fair enough :)
-
Maurício almost 11 years@Pranav Re-reading the question I agree you're right - you did answer the OPs query, so +1 :)