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
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
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.
18 People are following this question.
