Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Concatenation Cursor Expand / Collapse
Author
Message
Posted Tuesday, February 16, 2010 7:08 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:51 AM
Points: 4,158, Visits: 5,556
Juan de Dios (2/16/2010)
This script is very useful when all the rows are not NULL, but if you modify the insert of Mark with NULL, the script only returns Bill.

insert into @table values (4, NULL,'M')

Result: Bill

Where John goes???


Remember, in SQL, anything concat NULL = NULL,
therefore, when the NULL is encountered, the value is made NULL, then the next string is added. Since the definition is ISNULL(@var,''), the next value is added to the now empty string, delivering the last value

To cate for this, an aditional clause should be added to the predicate, viz:
AND Name IS NOT NULL



____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #866082
Posted Tuesday, February 16, 2010 7:14 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 14, 2014 7:14 AM
Points: 487, Visits: 261
Nice question. I guessed at the answer but lucked out.

I already have a use for this in a couple of procedures today. PERFECT TIMING!!
Post #866090
Posted Tuesday, February 16, 2010 7:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 10:51 AM
Points: 1,005, Visits: 143
As a couple of comments stated, the names may not come back in the order you want. On the same server, I ran this query at different times and got "John, Mark, Bill", "John, Bill, Mark" and "Bill, John, Mark" .
Post #866093
Posted Tuesday, February 16, 2010 7:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277

Juan de Dios (2/16/2010)
--------------------------------------------------------------------------------
This script is very useful when all the rows are not NULL, but if you modify the insert of Mark with NULL, the script only returns Bill.

insert into @table values (4, NULL,'M')

Result: Bill

Where John goes???


stewartc-708166

To cate for this, an aditional clause should be added to the predicate, viz:
AND Name IS NOT NULL


In addition or an alternative to stewartc excellent suggestion, you can in effect negate the presence of the NULL by using an ORDER BY [name] clause

More interesting try the code with a BLANK name, without the ORDER BY clause it appears in the list of names as , ,
- with the ORDER BY clause it appears as a leading comma before Bill.

sanbornd
An EXCELLENT QOD thanks for devising it and submitting, may I encourage you to submit additional QODs


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #866118
Posted Tuesday, February 16, 2010 8:25 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 6:50 AM
Points: 540, Visits: 254
Here's another alternative using STUFF and FOR XML PATH that does not require a parameter (I believe this is from an article from SSC. If I could have found the URL I would have included it in this post in order to give the appropriate credit.)

SELECT STUFF((SELECT ', ' + name
FROM @table
WHERE gender = 'M'
FOR XML PATH('')), 1, 2, '')
Post #866145
Posted Tuesday, February 16, 2010 8:48 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
A really good QotD! Thanks.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #866162
Posted Tuesday, February 16, 2010 8:55 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 18,064, Visits: 16,099
Nice question indeed. Thanks for submitting it.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #866166
Posted Tuesday, February 16, 2010 10:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 11, 2014 11:46 AM
Points: 249, Visits: 104
In addition to the comment about the missing order by, the result of this is also dependent on the CONCAT_NULL_YIELDS_NULL option. If this option is OFF, the argument to IsNull() will NOT be null, and therefore the result would be: ", John, Mark, Bill" -- leading comma.
Post #866253
Posted Tuesday, February 16, 2010 11:35 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 12:55 AM
Points: 771, Visits: 504
Excellent question.

And thanks all for the additional information in your posts.

--
Kevin C.
Post #866279
Posted Tuesday, February 16, 2010 3:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 9:43 PM
Points: 167, Visits: 280
bitbucket-25253 (2/16/2010)

Juan de Dios (2/16/2010)
--------------------------------------------------------------------------------
This script is very useful when all the rows are not NULL, but if you modify the insert of Mark with NULL, the script only returns Bill.

insert into @table values (4, NULL,'M')

Result: Bill

Where John goes???


stewartc-708166

To cate for this, an aditional clause should be added to the predicate, viz:
AND Name IS NOT NULL


In addition or an alternative to stewartc excellent suggestion, you can in effect negate the presence of the NULL by using an ORDER BY [name] clause

More interesting try the code with a BLANK name, without the ORDER BY clause it appears in the list of names as , ,
- with the ORDER BY clause it appears as a leading comma before Bill.

sanbornd
An EXCELLENT QOD thanks for devising it and submitting, may I encourage you to submit additional QODs


If you used this in the where clause AND ISNULL([name],'') <> '' you will negate both of the issues above.

A very good QOD which I have used a couple of times, but should be able to use more often

Post #866537
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse