SQL concatenate - please help - 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

SQL concatenate - please help

Author

Topic

scottydogg84
Starting Member

5 Posts

Posted - 2012-08-09 : 06:40:42

I have the following fields that I would like to join together with a new line in between:

SELECT     fmsaddr_1.fm_clinum, fmsaddr_1.fm_contac, fmsaddr_1.fm_addree, fmsaddr_1.fm_addli1, fmsaddr_1.fm_addli2, fmsaddr_1.fm_addli3, fmsaddr_1.fm_addli4, fmsaddr_1.fm_poscod
FROM       axxia01.dbo.fmsaddr

I need to have fmsaddr_1.fm_clinum as it is then the following address fields (fmsaddr_1.fm_contac, fmsaddr_1.fm_addree, fmsaddr_1.fm_addli1, fmsaddr_1.fm_addli2, fmsaddr_1.fm_addli3, fmsaddr_1.fm_addli4, fmsaddr_1.fm_poscod) all joined together each on a separate line.

I tried fmsaddr_1.fm_contac + CHAR(10) + fmsaddr_1.fm_addree + CHAR(10) + fmsaddr_1.fm_addli1 etc but the problem I have is that any one of these 7 fields could be NULL or = '' which would return a NULL value.

If any of these fields are NULL or blank then, if possible, I would also like to have the all blank rows removed in the concatenated field so i don't have blank rows in between those rows that aren't blank.

Any help would be much appreciated.

Thanks in advance

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-09 : 06:42:32

fmsaddr_1.fm_contac + coalesce(CHAR(10) + fmsaddr_1.fm_addree,'') + coalesce(CHAR(10) + fmsaddr_1.fm_addli1,'')

you might need char(13) + char(10)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources