using COALESCE to contcatenate strings

Question

using COALESCE to contcatenate strings

BitWise MnM

SSC Eights!

Points: 919
More actions
March 29, 2006 at 10:54 am

#111510

Can anyone point me to a MS resourse that describes this functionality of COALESCE? BOL just talks about replacing NULLS. Meanwhile much more complex operations are possible. E.g.

SELECT ID, COALESCE(
NULLIF ( address_1, '') + ',' + NULLIF ( address_2, '') + ',' + NULLIF ( address_3, '') ,
NULLIF ( address_1, '') + ',' + NULLIF ( address_2, '') ,
NULLIF ( address_1, '') + ',' + NULLIF ( address_3, '') ,
NULLIF ( address_2, '') + ',' + NULLIF ( address_3, '') ,
NULLIF ( address_1, '') ,
NULLIF ( address_2, '') ,
NULLIF ( address_3, '') ,
'' ) as Address
FROM #r
taken from http://www.umachandar.com/technical/SQL6x70Scripts/Main58.htm

Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Charles Otten Default port Points: 1475 More actions · Answer 1

Coalesce does exactly what you describe. It returns the first non null value encountered and a null value if there is not a non-null value found in the parameter string.

The example function returns the first group of addresses that all have values. So if the record in question only has a value in address_2 that is returned. If it has a value in address_2 and address_3 then those are returned separated by a comma.

Make Sense?

If the phone doesn't ring...It's me.

BitWise MnM SSC Eights! Points: 919 More actions · Answer 2

Yes, you are right, this is not the most perplexing example. How about this one?

It takes values from #repl_text_holder (with two columns line_no (int identity (1,1)) and sp_text varchar) and concotenates them into a string variable @var. Incidentally, when it does this, @var gets cut off at 4000 char

declare @var varchar(8000)

select @var=coalesce(@var + char(13),'') + sp_text

from #repl_text_holder

order by line_no

select @var

Charles Otten Default port Points: 1475 More actions · Answer 3

I believe the concatenation limitation comes from the definition of the varchar field in the column. I could definitely be wrong about that.

The example you show here is concatenating the values for each row in sp_text to the @var variable. It is also adding a carriage return between records. The problem is if there are any records with a null sp_text. This will cause anything added to @var to be cleared out because null plus anything is null.

Help?

c

If the phone doesn't ring...It's me.

BitWise MnM SSC Eights! Points: 919 More actions · Answer 4

well, there are two issues here, about null values :

there are none.

select count(*) from #repl_text_holder where sp_text is null - returns 0

and even if there were any I run this with SET CONCAT_NULL_YIELDS_NULL OFF

the second issue is the one that I really started this thread about.

If you try to run

declare @var varchar(8000)

select @var=@var + sp_text

from #repl_text_holder

order by line_no

select @var

You'll get nowhere.

Obviously it is the coalesce function that allows me to step through the table row by row and concatenate the values into one string. Yet, I seem to find nothing in BOL that documents ability of COALESCE to act in this way...

Charles Otten Default port Points: 1475 More actions · Answer 5

It's not the coalesce statement doing that. It's the structure of the SQL statement

The issue is setting a variable equal to itself plus a field value in a select statement.

This is what concatenates all the records into a single string.

In your last example you get nowhere because @var does not have an initialization value. @var starts out null and no matter what you add to it you'll get a null value.

c

If the phone doesn't ring...It's me.

BitWise MnM SSC Eights! Points: 919 More actions · Answer 6

Thank you so much,

eithere set @var='' or set concut_null_yields_null off produces desired results without coalesce. Which makes me wonder what it was doing there in the first place...

Anyhow, thank again. If you have any idea as to why the string gets truncated, I would love to hear about it.

vadba SSChampion Points: 11133 More actions · Answer 7

Mordechai, can you post the CREATE TABLE statement for #repl_text_holder?

As Charles stated previously, sp_text is probably defined as an nvarchar.

If so, you would either need to change that to varchar, or change you query to convert to varchar:

SELECT @var = Coalesce(@var + Char(13), '') + CONVERT(varchar(4000), sp_text)

FROM #repl_text_holder

ORDER BY line_no

BitWise MnM SSC Eights! Points: 919 More actions · Answer 8

Thanks,

I feel a bit not so smart, but even though I looked through it million times, I missed it. sp_text was defined as nvarchar. Now that I changed it to this:

create table #repl_text_holder (

[line_no] [int] IDENTITY (1, 1) NOT NULL ,

[sp_text] [varchar] (1000)

)

I get an expected behavior.