Concatenate Address Columns - 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 2005 Forums

Transact-SQL (2005)

Concatenate Address Columns

Author

Topic

starnold
Yak Posting Veteran

83 Posts

Posted - 2007-07-17 : 05:48:14

All

I need to concatenate 4 address fields into 1 Address field, I currently have:

Street1
Street2
Street3
Street4

I want them all in one field seperated by a comma:

Street1,Street2,Street3,Street4

The field are populated in descending order i.e. if Street3 is empty so will Street4. So what I don't want to do is have a comma when the next Street is empty

Cheers

Mat

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-17 : 05:55:19

[code]SELECT ISNULL(Street1 + ',', '') +
ISNULL(Street2 + ',', '') +
ISNULL(Street3 + ',', '') +
ISNULL(Street4, '')[/code]

KH
[spoiler]Time is always against us[/spoiler]

starnold
Yak Posting Veteran

83 Posts

Posted - 2007-07-17 : 07:18:35

thanks for that but it is still giving me a , when the next field is empty?

16 Bute Terrace,

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-07-17 : 09:42:33

declare @Street varchar(100)
set @Street = (
SELECT ISNULL(Street1 + ',', '') +
ISNULL(Street2 + ',', '') +
ISNULL(Street3 + ',', '') +
ISNULL(Street4, '')
)
if right(@Street, 1) = ',' set @Street = left (@Street, len(@Street)-1)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-17 : 09:45:32

[code]SELECT ISNULL(Street1, '') +
ISNULL(',' + Street2, '') +
ISNULL(',' + Street3, '') +
ISNULL(',' + Street4, '')[/code]

KH
[spoiler]Time is always against us[/spoiler]

starnold
Yak Posting Veteran

83 Posts

Posted - 2007-07-17 : 10:23:14

Superb thanks for your help khtan :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources