Remove , using Replace - 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

General SQL Server Forums

New to SQL Server Programming

Remove , using Replace

Author

Topic

vijaynetpart
Starting Member

18 Posts

Posted - 2009-06-08 : 05:56:21

i have a 3 columns Address1, Address2, Address3..All are optional..

I have concatenated the columns....

Address1+','+Address2+','+Address3

I like to remove the , which are not have a value...

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-08 : 05:58:47

try this..

isnull(Address1,'')+isnull(Address2,'')+isnull(Address3,'')

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-08 : 06:18:06

coalesce(Address1+',','')+
coalesce(Address2+case when address3 is not null then ',' else '' end,'')+
coalesce(address3,'')

Madhivanan

Failing to plan is Planning to fail

vijaynetpart
Starting Member

18 Posts

Posted - 2009-06-08 : 08:17:20

Address1 Address2 Address3
Add1 - Add3
Add1 Add2 -
- Add2 Add3

I like to get below result

Add1,Add3
Add1,Add2
Add2,Add3

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-08 : 08:34:29

Have u tried the above solution?? It will work for your case.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-08 : 09:05:57

have u expect with ','

Try this...

isnull(Address1+',','')+isnull(Address2+',','')+isnull(Address3+',','')

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

vijaynetpart
Starting Member

18 Posts

Posted - 2009-06-08 : 09:22:05

Senthil plz understamd my question.....

I found the result..........

Create Table #tmp
(
Address1 nVarchar(20),
Address2 nVarchar(20),
Address3 nVarchar(20)
)

Insert Into #tmp(Address1,Address2,Address3)

Select 'Add1','Add2','Add3'
Union All
Select 'Add1',Null,'Add3'
Union All
Select 'Add1','Add2',Null
Union All
Select Null,'Add2','Add3'
Union All
Select Null,Null,'Add3'
Union All
Select Null,'Add2',Null
Union All
Select 'Add1',Null,Null
Union All
Select Null,Null,Null

Select * From #tmp

Select Replace(Replace(Replace(IsNull(Address1,',-')+','+IsNull(Address2,',-')+','+IsNull(Address3,'-,'),',-,',''),',-,',''),'-,','')
From #tmp

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-08 : 09:42:37

try this

select
isnull(reverse(stuff(reverse(isnull(Address1+',','')+isnull(Address2+',','')+isnull(Address3+',','')),1,1,'')),'')
from #tmp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources