Computed Column (COALESCE vs CASE vs ISNULL)

Question

I posted a similar question a while back and now as I need to update this code, I am back to ask a follow-on question. Previous question is here:
Computed column based on nullable columns

My data (Address1, Address2, City, State, Zip, Country) may have incomplete information. I.e. I can't be guaranteed that anything other than State and Country columns will have data.

I would like to have a computed columns for FullAddress.

Previously, I used COALESCE, which worked great if all fields are filled in. Now, as the data requirements have been relaxed, this is no longer an option (because we end with repeated commas in the FullAddress). Here is what I have been using previously (note, I'm just working with SELECT statements here for ease of use - will convert into a computed columns "alter table add" statement once I have something that works for all cases):

SELECT (((((COALESCE([Address1],'')
    + COALESCE(', '+[Address2],''))
    + COALESCE(', '+[City],''))
    + COALESCE(', '+[State],''))
    + COALESCE(', '+[Zip],''))
    + COALESCE(', '+[Country],'')) AS FullAddress
FROM Locations

Now, I have put together an alternative using CASE, but it still doesn't work for the edge case where Address1 is NULL (the problem is that the FullAddress will have ', ' as the first two characters)

SELECT CASE WHEN [Address1] IS NOT NULL THEN [Address1] ELSE '' END
        + CASE WHEN [Address2] IS NOT NULL THEN ', ' + [Address2] ELSE '' END
        + CASE WHEN [City] IS NOT NULL THEN ', ' + [City] ELSE '' END
        + CASE WHEN [State] IS NOT NULL THEN ', ' + [State] ELSE '' END
        + CASE WHEN [Zip] IS NOT NULL THEN ', ' + [Zip] ELSE '' END
        + CASE WHEN [Country] IS NOT NULL THEN ', ' + [Country] ELSE '' END
        AS [FullAddress]
FROM Locations

I'm a little stuck at this point. Any recommendations what to try next?

Answer 1

you can use this pattern:

SELECT
    ISNULL(Address1 + ', ', '')
    + ISNULL(Address2 + ', ', '')
    + ISNULL(City + ', ', '')
    -- ....
    AS FullAddress

The result of concation NULL + ', ' is NULL => Address1 + ', ' will be NULL or valid address => ISNULL(Address1 + ', ', '') will be empty string or valid address.