August 9, 2012 at 4:43 am
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
August 9, 2012 at 4:58 am
If you have a blank (''), then you want to lose the CHAR(10) so that you don't end up with a blank line, so use a CASE expression to deal with that. You can use COALESCE or ISNULL to deal with NULLs. Have a go at that, and post back if there's anything you don't understand.
John
August 9, 2012 at 7:42 am
If you validate your blanks with LEN(field) > 0, you don't need to worry about the nulls.
SELECT CASE WHEN LEN(fm_clinum) > 0 THEN fm_clinum + CHAR(10) ELSE '' END +
CASE WHEN LEN(fm_contac) > 0 THEN fm_contac + CHAR(10) ELSE '' END +
...
FROM axxia01.dbo.fmsaddr
If you have columns that are not strings, you must use a case/convert statement.
August 9, 2012 at 8:05 am
I think I would take the ISNULL approach. Something like this:
SELECT ISNULL(fm_clinum, '') + ISNULL(fm_contac, '')
...
FROM axxia01.dbo.fmsaddr
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 9, 2012 at 8:12 am
That was my first thought Sean, but he wanted to identify the empty strings to avoid additional lines.
August 9, 2012 at 8:14 am
Luis Cazares (8/9/2012)
That was my first thought Sean, but he wanted to identify the empty strings to avoid additional lines.
Missed that part.
This should do it.
SELECT ISNULL(fm_clinum + CHAR(10), '') + ISNULL(fm_contac + CHAR(10), '')
...
FROM axxia01.dbo.fmsaddr
This all seems like formatting stuff that really should be left to the front end imo.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 9, 2012 at 8:18 am
Another option, could be
SELECT ISNULL(NULLIF(fm_clinum, '') + CHAR(10), '') +
ISNULL(NULLIF(fm_contac, '') + CHAR(10), '')
...
FROM axxia01.dbo.fmsaddr
August 9, 2012 at 8:22 am
Luis Cazares (8/9/2012)
Another option, could be
SELECT ISNULL(NULLIF(fm_clinum, '') + CHAR(10), '') +
ISNULL(NULLIF(fm_contac, '') + CHAR(10), '')
...
FROM axxia01.dbo.fmsaddr
Such a strange requirement this is but I think that captures all the rules as I read them. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 9, 2012 at 8:33 am
Thanks all - really appreciate your time and help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply