Computed Column Specification for full address

Question

I have a table with multiple address columns

Address1 | Address2 | Address3 | Address4 | City | County | PostCode

Address1 and PostCode will always be populated, the other columns may contain null. I want to have a computed column that can hold the full address, excluding null columns, but inserting char(10) and char(13) linefeeds after each column that is not null.

Coalesce and ISNULL don't allow me to add the linefeeds only after non null columns, and CASE WHEN ELSE END returns null if any one column contains null.

Can this be done?

Answer 1

You can use CONCAT_WS for this. The nulls are ignored, and each one is separated by the separator value (the first parameter).

ALTER TABLE YourTable
ADD FullAddress AS (
  CONCAT_WS('
', Address1, Address2, Address3, Address4, City, County, PostCode
);

Answer 2

select 
    case when isnull(Address1,'')='' then '' else Address1+char(10) end
    + case when isnull(Address2,'')='' then '' else Address2+char(10) end 
    + case when isnull(Address3,'')='' then '' else Address3+char(10) end    
    + case when isnull(Address4,'')='' then '' else Address4+char(10) end     
    + case when isnull(City,'')='' then '' else City+char(10) end     
    + case when isnull(County,'')='' then '' else County+char(10) end     
    + case when isnull(PostCode,'')='' then '' else PostCode+char(10) end    full_name
from `table_name`

Answer 3

you can use the CONCAT() function along with COALESCE() function to concatenate non-null values and CHAR() to insert linefeeds.

SELECT Address1,Address2,Address3,Address4,City,County,PostCode,CONCAT(
    COALESCE(Address1 + CHAR(13) + CHAR(10), ''),
    COALESCE(Address2 + CHAR(13) + CHAR(10), ''),
    COALESCE(Address3 + CHAR(13) + CHAR(10), ''),
    COALESCE(Address4 + CHAR(13) + CHAR(10), ''),
    COALESCE(City + CHAR(13) + CHAR(10), ''),
    COALESCE(County + CHAR(13) + CHAR(10), ''),
PostCode) AS FullAddress
FROM table_name;