concatenating columns

[Eyespi20]

I'm trying to concatenate some nullable columns into one column.

Am using coalesce, but it's not returning the expected values. Can someone take a look at this code and advise me where I've gone wrong?

update msn_newCall set notify = COALESCE(
	NULLIF ( arc, '') + ';' + NULLIF (billto, '') + ';' + NULLIF ( place, '') ,
	NULLIF ( arc, '') + ';' + NULLIF ( billto, '') ,
	NULLIF ( arc, '') + ';' + NULLIF ( place, '') ,
	NULLIF ( billto, '') + ';' + NULLIF ( place, '') ,
	NULLIF ( arc, '') ,
	NULLIF ( billto, '') ,
	NULLIF ( place, '') ,
	 '')
 FROM msn_newcall join #mailto on msn_newcall.request_id = #mailto.request_id

I EXPECT to get:
arc; place; billto
arc; place
arc; billto
place; billto
place; billto
arc
place
billto


however, I'm getting:
arc; place; billto
arc;place ;
arc;; billto
;place;billto
arc;
;place;
;;billto

Thanks!

Margaret

 

[bborissov]

Can you post table structure, some simple data and what result you want from that data, becuase I am lost in NULLIFs

Borislav Borissov

 

[Eyespi20]

sure

#mail table has
request_id, place_id, arc, place, billto
all columns are varchar of varying lengths. The arc, place and billto contain email addresses from various tables in my database

the update table has request_id,place_id, notify which is the column I'm updating to contain those concatenated email addresses with the purpose of sending email to those people.

data:
1023456, 763542, joe@somewhere.com, null, cathy@somewhereelse.com
1023444, 802345, ann@somewhere.com, alex@trident.com, cathy@somewhereelse.com
1023422, 753245, sally@somewhere.com, null, null
1023433, 723459, kim@somewhere.com, kim@florida.net, sonja@myplace.com

Thanks again.

Margaret

 

[vongrunt]

Are blank strings ('') in data possible instead of NULLs?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Eyespi20]

They are, but I update the temp table to make them null if they exist before I run this concantenation string.

update #mailto set arc= case when arc='' then null else arc end, billto=case when billto='' then null else billto end, place=case when place='' then null else place end

Margaret

 

[vongrunt]

How about (assuming SET CONCAT_NULL_YIELDS_NULL is ON (default)):

update msn_newCall 
	set notify = isnull( arc + '; ', '') + isnull( place + '; ', '') + isnull( billto + '; ', '')
FROM msn_newcall join #mailto on msn_newcall.request_id = #mailto.request_id

?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[bborissov]

update msn_newCall
       set notify = ISNULL(#mail.arc        ,'')+
                    ISNULL(';' + #mail.place,'')+
                    ISNULL(';' + #mail.billto,'')
FROM msn_newcall
join #mailto on msn_newcall.request_id = #mailto.request_id

if you want [;] to be there no metter if the field is null or not just chnage this to:

update msn_newCall
       set notify = ISNULL(#mail.arc   , '') + ';' +
                    ISNULL(#mail.place , '') + ';' +
                    ISNULL(#mail.billto, '')
FROM msn_newcall
join #mailto on msn_newcall.request_id = #mailto.request_id

Borislav Borissov

 

[Eyespi20]

No, both of these solutions still result in my having ';' if there is a null email address.

I think I'm going to have to do a case statement rather than coalesce.

something like:

update msn_newcall set
  notify = case when #mailto.arc is not null and #mailto.place is null and #mailto.billto is null then #mailto.arc 
          when #mailto.arc is not null and #mailto.place is not null and #mailto.billto is null then #mailto.arc + ' ;' + #mailto.place 
          when #mailto.arc is not null  and #mailto.place is null and #mailto.billto is not null then #mailto.arc + ' ;' + #mailto.billto
          when #mailto.arc is null and #mailto.place is not null and #mailto.billto is not null then #mailto.place + ' ;' + #mailto.billto
          when #mailto.arc is null and #mailto.place is null and #mailto.billto is not null then #mailto.billto
          when #mailto.arc is null and #mailto.place is null and #mailto.billto is null then null end
from #mailto join msn_newcall on #mailto.request_id = msn_newcall.request_id

Yeah -- that works.

Margaret

 

[vongrunt]

That would become ugly with 4 or more e-mails... 15 CASE/WHEN combos?

If trailing semicolon really must be removed, I would rather do something like:

update msn_newCall
    set notify = left(X.composedNotify, len(X.composedNotify)-2)
FROM msn_newcall 
join 
(	select request_id, 
		isnull( arc + '; ', '') + isnull( place + '; ', '') + isnull( billto + '; ', '') as composedNotify
	from mailto
) X
on msn_newcall.request_id = X.request_id

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[bborissov]

With that this statement:

CREATE TABLE #mail (cTest varchar(8000), arc varchar(200) NULL, place varchar(200) NULL, billto varchar(200) null)

INSERT INTO #mail VALUES ('','joe@somewhere.com', null, 'cathy@somewhereelse.com')
INSERT INTO #mail VALUES ('','ann@somewhere.com', 'alex@trident.com', 'cathy@somewhereelse.com')
INSERT INTO #mail VALUES ('','sally@somewhere.com', null, null)
INSERT INTO #mail VALUES ('','kim@somewhere.com', 'kim@florida.net', 'sonja@myplace.com')

update #mail
       set cTest = ISNULL(#mail.arc       ,'')+
                   ISNULL(';'+#mail.place ,'')+
                   ISNULL(';'+#mail.billto,'')
SELECT * FROM #mail
DROP TABLE #mail

I have this result:

cTest
-----------------------------------------------------------
joe@somewhere.com;cathy@somewhereelse.com
ann@somewhere.com;alex@trident.com;cathy@somewhereelse.com
sally@somewhere.com
kim@somewhere.com;kim@florida.net;sonja@myplace.com

Borislav Borissov

 

[vongrunt]

That will give leading ';' if arc is NULL.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Eyespi20]

Thanks, but in this instance, there are only the 3 columns that I need to concatenate, so this case statement will work just fine.

Besides, it's not just trailing semicolons, it's doubles in the middle and leading as well. SQL Mail (I've found) want's to barf when you have empty space where it expects there to be an email address and that makes my whole job fail. NOT a pretty sight!

Thanks for putting your minds to this, Vongrunt and bborissov! I really appreciate your input. Both give me something to look at for future projects where I won't be limited to 3 columns.

Margaret