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 NULL13 Dog Cheese14 NULL Milk15 Moose Better16 Goose Pepper17 NULL Salt18 NULL MustardI want to concatenate all the rows into a single string (comma delimited) to look like this: 12,Cat,NULL13,Dog,Cheese14,NULL,Milk15,Moose,Better16,Goose,Pepper17,NULL,Salt18,NULL,MustardWhen I run this query:SELECT CAST (A+","+B+","+C as varchar(1000)) as test I get this:NULL13,Dog,CheeseNULL15,Moose,Better16,Goose,PepperNULLNULLI 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,'') |
|
|
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] |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-26 : 15:07:12
|
Use sodeep's code. I mis-read your requirement. |
|
|
Johnph
Posting Yak Master
103 Posts |
Posted - 2012-12-26 : 15:18:29
|
Giving this a shot now |
|
|
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? |
|
|
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. |
|
|
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.seehttp://msdn.microsoft.com/en-us/library/ms188048.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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! |
|
|
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. |
|
|
|