Concatenate fields (suppress separators)

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

Concatenate fields (suppress separators)

Author

Topic

StewartS
Starting Member

2 Posts

Posted - 2009-10-06 : 06:27:36

Hi there, I'm trying to concatenate address fields in order to create a "FullAddress" field. I'm doing this in a SQL view, as I only need "FullAddress" to populate a drop-down list in a web form (got this working fine).

The problem is that, where there are address fields which as null, the result looks like this:
10 High Street, , , London, W1 1SQ

Is there a way to modify the SQL statement so that the redundant commas are ignored (i.e. 10 High Street, London, W1 1SQ).

At the moment, my SQL statement is as follows:

CREATE VIEW dbo.viewOfficeName
AS
SELECT DISTINCT COALESCE (Address1, '') + ', ' + COALESCE (Address2, '') + ', ' + COALESCE (Address3, '') + ', ' + COALESCE (Town, '') + ', ' + COALESCE (Postcode, '')
AS FullAddress
FROM dbo.tblServices

Any help would be appreciated!

Cheers,
Stewart

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-06 : 06:53:32

CREATE VIEW dbo.viewOfficeName
AS
SELECT DISTINCT COALESCE (Address1+' ,', '') + COALESCE (Address2+' ,', '') + COALESCE (Address3+' ,', '') + COALESCE (Town+' ,', '') + COALESCE (Postcode, '')
AS FullAddress
FROM dbo.tblServices

Madhivanan

Failing to plan is Planning to fail

StewartS
Starting Member

2 Posts

Posted - 2009-10-06 : 07:10:35

Thanks Madhivanan, works perfectly!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-06 : 08:20:00

More accurately

CREATE VIEW dbo.viewOfficeName
AS
SELECT DISTINCT COALESCE (Address1+case when Address1 is null then '' else ', ' end, '') + COALESCE (Address2+case when Address3 is null then '' else ', ' end, '') + COALESCE (Address3+case when Town is null then '' else ', ' end, '') + COALESCE (Town+case when Postcode is null then '' else ', ' end, '') + COALESCE (Postcode, '')
AS FullAddress
FROM dbo.tblServices

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources