Concatenation, Coalesce and Commas

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)

Concatenation, Coalesce and Commas

Author

Topic

francism
Starting Member

22 Posts

Posted - 2008-08-13 : 11:32:29

[code]
-- Create a test table
Declare @testTable table (addr1 varchar(10), addr2 varchar(10), addr3 varchar(10))

-- Insert some sample values
Insert into @testTable values ('aaa','bbb','ccc')
Insert into @testTable values ('ddd','eee',null)
Insert into @testTable values ('fff',null,null)

-- Select the values
SELECT
COALESCE (addr1, addr2, addr3) + ' ' + COALESCE (addr2, addr3) + ' ' + COALESCE (addr3, '') as addr
from @testTable
[/code]

I would like a comma between the concatenated strings only if the next string is not null.

This is what I get now:
[code]
aaa bbb ccc
ddd eee
NULL
[/code]

This is what I'd like:
[code]
aaa, bbb, ccc
ddd, eee
NULL
[/code]

There's the ISNULL function, but it seems I need a ISNOTNULL function to resolve this.

Any ideas?

Thanks,

Francis.

Microsoft CRM 3 - SQL Server 2000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 12:04:31

SELECT
COALESCE(COALESCE (addr1, addr2, addr3) + ', ','') +
COALESCE(COALESCE (addr2, addr3) + ', ','') +
COALESCE (addr3, '') as addr
from @testTable

francism
Starting Member

22 Posts

Posted - 2008-08-13 : 13:30:15

Vishak16,

Thanks for your reply, but your solution is not exactly what I'm looking for - it puts a comma at the end of the string:


aaa, bbb, ccc
ddd, eee, 
fff,

What I'm hoping to achieve is:


aaa, bbb, ccc
ddd, eee 
fff

Microsoft CRM 3 - SQL Server 2000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 13:36:33

sorry it should other way round


SELECT 
COALESCE (addr1, addr2, addr3) + 
COALESCE(',' + COALESCE (addr2, addr3),'') + 
COALESCE (',' +  addr3, '') as addr
from @testTable

francism
Starting Member

22 Posts

Posted - 2008-08-13 : 14:06:33

Vishak16,

Thanks very much - that's exactly what I need.

Francis.

Microsoft CRM 3 - SQL Server 2000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 14:18:39

quote:
Originally posted by francism

Vishak16,

Thanks very much - that's exactly what I need.

Francis.

Microsoft CRM 3 - SQL Server 2000

welcome

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-14 : 06:47:30

or

SELECT
replace(rtrim(COALESCE (addr1, addr2, addr3) + ' ' + COALESCE (addr2, addr3,'') + ' ' + COALESCE (addr3, '')),' ',',') as addr
from @testTable

Madhivanan

Failing to plan is Planning to fail

francism
Starting Member

22 Posts

Posted - 2008-08-14 : 10:22:18

More functions but simpler to understand... Thanks madhivanan.

Microsoft CRM 3 - SQL Server 2000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources