A question about COALESCE and Nulls

Question

Post reply

A question about COALESCE and Nulls

meichner

SSCertifiable

Points: 5563
More actions
August 31, 2010 at 11:10 am

#237496

I was trying to find the best way to contenate strings when I came across this solution.
Declare @myVariable varchar(max)
Select @myVariable = coalesce(@myVariable + ',', '') + MyColumn
From MyTable
So I adapted this solution to my tables and came up with the following:
Declare @Notes VarChar(Max)
Select @Notes = COALESCE(@Notes + ',' ,'') + Note
From DesignRequestsHistory Where DesignRequestNumber = 540
select @Notes
The data in this table is:
Note
[NULL]
THIS IS TEST
[NULL]
HELP !!!!!!!!!!
YO MAMMA
[NULL]
FOO
PUT ON HOLD FOR TEST
RE SUBMIT
A TEST
[NULL]
[NULL]
BATHBODY
I thought that I would get a comma seperated set of values, but instead the result was the last row - BATHBODY
In order to get the comma separated values I had to add Note <> '' to the Select statement.
declare @Notes VarChar(Max)
Select @Notes = COALESCE(@Notes + ',' ,'') + Note
From DesignRequestsHistory Where DesignRequestNumber = 540 and Note <> ''
select @Notes
This returned what I expected:
THIS IS TEST,HELP !!!!!!!!!!,YO MAMMA,FOO,PUT ON HOLD FOR TEST,RE SUBMIT,A TEST,BATHBODY
So my question really is no longer about how to concatenate strings or which is the best method for doing so. My question is why did I need to add the Note <> '' to the selection criteria? I thought using the COALESCE would handle the nulls.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

Shouldn't the coalesce be around the column, not the variable?

declare @Notes VarChar(Max)

Select @Notes = @Notes + COALESCE(Note + ',' ,'')

From DesignRequestsHistory Where DesignRequestNumber = 540

select @Notes

Also you may have to set concat_null_yields_null to false.

And then again, I might be wrong ...
David Webb

WayneS SSC Guru Points: 95460 More actions · Answer 2

David Webb-200187 (8/31/2010)
Shouldn't the coalesce be around the column, not the variable?

For the way it's coded, it actually needs to be around BOTH.

declare @Notes VarChar(Max)

Select @Notes = COALESCE(@Notes + ',','') + COALESCE(Note,'')

From DesignRequestsHistory Where DesignRequestNumber = 540

select @Notes

Here's why.

First, the COALESCE around the @Notes is handling whenever the @Notes is null. You are expecting this to only be NULL the first time you process the record set. Since @Notes is declared but never initialized, you need this.

HOWEVER, when you hit a record where the Note column is null, the resulting string is NULL (@Notes + NULL = NULL). When it then goes to the next record, @Notes is NULL again, and starts again as an empty string, and then adding the Notes column to it again.

By putting the Notes column in a COALESCE, you force the result to never be NULL.

Note that if you add an additional not-null entry after the current last one, you would get the two entries with a comma between them.

BTW, for a faster way of doing this, check out this article: Using XML to Enhance the Performance of String Manipulations[/url].

BTW2: a better way to add to the where clause would be AND Note IS NOT NULL instead of AND Note <> ''

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

David Webb-CDS SSCoach Points: 17398 More actions · Answer 3

That's why I suggested setting concat_null_yields_null to false. It will prevent the concatenation of nulls from resulting in a null string. Safer to do it your way, since that will work under all circumstances.

And then again, I might be wrong ...
David Webb

meichner SSCertifiable Points: 5563 More actions · Answer 4

WayneS (8/31/2010)
David Webb-200187 (8/31/2010)
Shouldn't the coalesce be around the column, not the variable?
For the way it's coded, it actually needs to be around BOTH.
declare @Notes VarChar(Max)
Select @Notes = COALESCE(@Notes + ',','') + COALESCE(Note,'')
From DesignRequestsHistory Where DesignRequestNumber = 540
select @Notes
Here's why.
First, the COALESCE around the @Notes is handling whenever the @Notes is null. You are expecting this to only be NULL the first time you process the record set. Since @Notes is declared but never initialized, you need this.
HOWEVER, when you hit a record where the Note column is null, the resulting string is NULL (@Notes + NULL = NULL). When it then goes to the next record, @Notes is NULL again, and starts again as an empty string, and then adding the Notes column to it again.
By putting the Notes column in a COALESCE, you force the result to never be NULL.
Note that if you add an additional not-null entry after the current last one, you would get the two entries with a comma between them.
BTW, for a faster way of doing this, check out this article: Using XML to Enhance the Performance of String Manipulations[/url].
BTW2: a better way to add to the where clause would be AND Note IS NOT NULL instead of AND Note <> ''

Thanks to both you and David. This now makes sense.