SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Concatenate Difficulties
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Johnph
Yak Posting Veteran

95 Posts

Posted - 12/26/2012 :  14:48:19  Show Profile  Reply with Quote
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

5155 Posts

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

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/26/2012 :  15:02:55  Show Profile  Reply with Quote
Isnull(cast(columnA as Varchar(40)),'NULL') + ',' + Isnull(ColumnB,'NULL') + ',' + Isnull(ColumnC,'NULL')
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

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

Johnph
Yak Posting Veteran

95 Posts

Posted - 12/26/2012 :  15:18:29  Show Profile  Reply with Quote
Giving this a shot now
Go to Top of Page

Johnph
Yak Posting Veteran

95 Posts

Posted - 12/26/2012 :  15:52:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/26/2012 :  16:07:20  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 12/26/2012 :  23:25:54  Show Profile  Reply with Quote
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
Yak Posting Veteran

95 Posts

Posted - 12/27/2012 :  09:43:33  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/27/2012 :  09:52:13  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000