question

SSGC avatar image
SSGC asked

multiple addresses with line break

Client want a column show multiple addresses. each address have a line break if there is a next address. I use a script for this column, but when some address is null or blank, it is still show the line break. Could you help me to resolve this issue? Thanks! CREATE TABLE #address ( address_1 VARCHAR(100) , address_2 VARCHAR(100) , address_3 VARCHAR(100) , address_4 VARCHAR(100) ) INSERT INTO #address ( address_1 , address_2 , address_3 , address_4 ) VALUES ( 'A1' , -- address_1 - varchar(100) 'B1' , -- address_2 - varchar(100) 'C1' , -- address_3 - varchar(100) 'D1' -- address_4 - varchar(100) ), ( NULL , -- address_1 - varchar(100) 'B2' , -- address_2 - varchar(100) NULL , -- address_3 - varchar(100) 'D2' -- address_4 - varchar(100) ), ( 'A3' , -- address_1 - varchar(100) NULL , -- address_2 - varchar(100) NULL , -- address_3 - varchar(100) NULL -- address_4 - varchar(100) ) SELECT ISNULL(ADDRESS_1, '') + CHAR(13) + CHAR(10) + ISNULL(ADDRESS_2, '') + CHAR(13) + CHAR(10) + ISNULL(ADDRESS_3, '') + CHAR(13) + CHAR(10) + ISNULL(ADDRESS_4, '') AS Billing_Address FROM #Address DROP TABLE #address
t-sqlstringnullisnull
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Add each address and the corresponding line break, conditionally on whether there is an address or not: SELECT case when address_1 is null then '' else ADDRESS_1 + CHAR(13) + CHAR(10) end + case when address_2 is null then '' else ADDRESS_2 + CHAR(13) + CHAR(10) end + case when address_3 is null then '' else ADDRESS_3 + CHAR(13) + CHAR(10) end + case when address_4 is null then '' else ADDRESS_4 + CHAR(13) + CHAR(10) end as Billing_Address FROM #Address
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SSGC avatar image SSGC commented ·
work great, thanks
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
While Kev's solution is close, it will end up with an extra CR/LF at the end of each address, which wasn't in the original spec... try this for an alternative: SELECT STUFF( case when address_1 is null then '' else char(13) + char(10) + ADDRESS_1 end + case when address_2 is null then '' else char(13) + char(10) + ADDRESS_2 end + case when address_3 is null then '' else char(13) + char(10) + ADDRESS_3 end + case when address_4 is null then '' else char(13) + char(10) + ADDRESS_4 END ,1,2,'') as Billing_Address FROM @Address It works in a similar way to @kev riley's solution, but puts the CRLF at the beginning of each row that's added, and then uses the STUFF command to remove the first CRLF.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
Ah yes. Much better!
1 Like 1 ·
SSGC avatar image SSGC commented ·
it works great! Thanks!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.