COALESCE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

COALESCE

Author

Topic

baaul
Starting Member

16 Posts

Posted - 2007-06-27 : 18:20:47

Hello,
I am trying to UPDATE [Sanction] field by CONCATing three column data eg.
==================================================================
BC_Sanction****PDS_Sanction*******Other_Sanction******Sanction
==================================================================
BC-------------PDS--------------------Other--------------BC,PDS,Other
BC-------------NULL-------------------OTHER---------------BC,Other
NULL-----------PDS--------------------Other---------------PDS,Other

My COALESCE function doesn't give me the expected result. Please see below: --->

UPDATE #TempTbl
SET #TempTbl.Sanction =
COALESCE(#TempTbl.BC_Sanction,#TempTbl.PDS_Sanction,#TempTbl.Other_Sanction)

Thanks in advance for any help.

-Baaul

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-27 : 20:40:48

How is it not giving you expected result?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-27 : 20:48:24

Check if this works:


select * ,
	Replace(Replace(isnull(BC_Sanction, 'N/A') + ',' + isnull(PDS_Sanction, 'N/A') + ',' + isnull(Other_Sanction, 'N/A') , ',N/A',''), 'N/A,', '')
from YourTable

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-27 : 21:17:30

There is a small caveat though. I am assuming none of your values have the value 'N/A' in them. If you do have values use some such string that you are sure does not exist in any of these tables.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

baaul
Starting Member

16 Posts

Posted - 2007-06-28 : 10:24:17

Actually I don't want the values displayed if ther's a null value i.e. if ===== NULL-----PDS---other THEN only PDS,other should disply.
it shouldn't be ----> null,pds,other

Another example: BC ----NULL---other THEN it should be like BC,other
Not BC,,other when I concat the fields. Any advice.

Thanks!

-Baaul

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-28 : 10:31:30

Try this:

UPDATE #TempTbl
SET #TempTbl.Sanction = 
COALESCE(#TempTbl.BC_Sanction + ',','') + COALESCE(#TempTbl.PDS_Sanction + ',','') + COALESCE(#TempTbl.Other_Sanction + ',', '')

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources