| Author |
 Topic  |
|
|
Johnph
Yak Posting Veteran
75 Posts |
 Posted - 12/26/2012 : 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?
|
Edited by - Johnph on 12/26/2012 14:50:16
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/26/2012 : 15:02:27
|
Do it like this:SELECT CAST('A' AS VARCHAR(1000)) + COALESCE(','+B,'') + COALESCE(','+C,'') |
 |
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/26/2012 : 15:02:55
|
Isnull(cast(columnA as Varchar(40)),'NULL') + ',' + Isnull(ColumnB,'NULL') + ',' + Isnull(ColumnC,'NULL') |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/26/2012 : 15:07:12
|
| Use sodeep's code. I mis-read your requirement. |
|
|
|
Johnph
Yak Posting Veteran
75 Posts |
Posted - 12/26/2012 : 15:18:29
|
| Giving this a shot now |
|
|
|
Johnph
Yak Posting Veteran
75 Posts |
Posted - 12/26/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/26/2012 : 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
India
47023 Posts |
Posted - 12/26/2012 : 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/
|
|
|
|
Johnph
Yak Posting Veteran
75 Posts |
Posted - 12/27/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/27/2012 : 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.
|
|
|
| |
Topic |
|
Concatenate Difficulties
