Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Concatenate Difficulties

Author  Topic 

Johnph
Posting Yak Master

103 Posts

Posted - 2012-12-26 : 14:48:19
I have ran into a road block and having some difficulties troubleshooting a concatenation.

In the table I have three columns (A, B, C) with information that looks like this:

12 Cat NULL
13 Dog Cheese
14 NULL Milk
15 Moose Better
16 Goose Pepper
17 NULL Salt
18 NULL Mustard


I want to concatenate all the rows into a single string (comma delimited) to look like this:

12,Cat,NULL
13,Dog,Cheese
14,NULL,Milk
15,Moose,Better
16,Goose,Pepper
17,NULL,Salt
18,NULL,Mustard

When I run this query:

SELECT CAST (A+","+B+","+C as varchar(1000)) as test

I get this:

NULL
13,Dog,Cheese
NULL
15,Moose,Better
16,Goose,Pepper
NULL
NULL


I have tried RTRIM and COALESCE but it has not seem to work. Does anyone have any ideas?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-26 : 15:02:27
Do it like this:
SELECT CAST('A' AS VARCHAR(1000)) + COALESCE(','+B,'') + COALESCE(','+C,'')
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-26 : 15:02:55
[code]Isnull(cast(columnA as Varchar(40)),'NULL') + ',' + Isnull(ColumnB,'NULL') + ',' + Isnull(ColumnC,'NULL')[/code]
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-26 : 15:07:12
Use sodeep's code. I mis-read your requirement.
Go to Top of Page

Johnph
Posting Yak Master

103 Posts

Posted - 2012-12-26 : 15:18:29
Giving this a shot now
Go to Top of Page

Johnph
Posting Yak Master

103 Posts

Posted - 2012-12-26 : 15:52:36
Thanks soooooo much guys. ISNULL worked like a charm.

I do have a newbie question, why doesn't it like the NULLS when you are concatenating? and why doesn't coalesce work?

Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-26 : 16:07:20
NULL is not really stored as the string "NULL" in the database, it is an indicator that there is no value.

NULL concatenated with anything returns a NULL no matter what the other values are. So when you do something like A+B+C, if at least one of them is NULL, you would get null.

So what sodeep was doing was looking at each piece to see if it is null, and if it is, replace it with the word NULL. That is what the expression ISNULL(ColumnB,'NULL') does. It returns columnB if it is not null and the word NULL if it is null.

You can use COALESCE(ColumnB,'NULL') as well. For the most part, they are equivalent in this case.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-26 : 23:25:54
To add to what Sunita said, default behavior in SQL server is to consider NULL as an indicator. This can however be changed by altering the ANSI NULLs setting. setting ANSI NULLs to off would consider NULL also as a value.

see

http://msdn.microsoft.com/en-us/library/ms188048.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Johnph
Posting Yak Master

103 Posts

Posted - 2012-12-27 : 09:43:33
I see... I have many years until I can gain a percentage of your knowledge guys. I guess if I set NULL as a value (ANSI NULLS) my concat would probably have worked.

Thanks again guys!
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-27 : 09:52:13
Yes - you could have set SET CONCAT_NULL_YIELDS_NULL OFF to OFF and it would have worked; however, it is not a recommended practice - see here: http://msdn.microsoft.com/en-us/library/ms176056.aspx Also, it would be treated as an empty string rather than the word NULL.

ANSI_NULLS ON/OFF determines the behavior when you do comparisons such as = or <>. ANSI NULLS OFF setting also is discouraged and is slated for removal.
Go to Top of Page
   

- Advertisement -