SQL Server – Concatenation with Separators for Nullable Fields

sql server

I have three columns, CITY, STATE, COUNTRY. One, two, or all columns may be NULL.

With the following logic, a single string is made using the SQL below. It is messy, and I am wondering if there is a simpler way to achieve the output? I've considered CONCAT() but I don't think I can use that and still get the separators in there.

  • if both CITY and STATE have a value, use that, separated by ', '. Exclude COUNTRY.
  • if only one of CITY or STATE exists, AND Country exists, use the one of CITY/STATE which exists, plus the separator, and COUNTRY.
  • if neither CITY or STATE exist, use country only with no separator
  • if only one of the three exists, use it, as is, with no separator
  • If none exist, output should be NULL (not '')

I will add that if a CITY and/or STATE value exists, then COUNTRY will never be NULL.

Here is the code so far. It is messy but I believe it meets the output requirements listed above.

I am working on SQL Server 2008.

CASE WHEN 
    CASE WHEN ISNULL(M.CITY, '') = '' THEN  '' ELSE ISNULL(M.CITY, '') + ', ' END + CASE WHEN ISNULL(M.PROVINCE, '') = '' THEN  '' ELSE ISNULL(M.PROVINCE, '') END

     = '' THEN CASE WHEN COUNTRY IS NOT NULL THEN COUNTRY ELSE NULL END ELSE 

     CASE WHEN ISNULL(M.CITY, '') = '' THEN  '' ELSE ISNULL(M.CITY, '') + ', ' END + CASE WHEN ISNULL(M.PROVINCE, '') = '' THEN  '' ELSE ISNULL(M.PROVINCE, '') END 
END 
+ 
CASE
    WHEN (M.CITY IS NULL AND M.PROVINCE IS NOT NULL) OR (M.CITY IS NOT NULL AND M.PROVINCE IS NULL) THEN 
        CASE WHEN PROVINCE IS NOT NULL AND CITY IS NULL THEN ', ' ELSE '' END + COUNTRY
    ELSE '' 
END 
LOCATION

Best Answer

Does this do what you need?

select coalesce(city + ', ' + state, 
                city + ', ' + country, 
                state + ', ' + country, 
                country, 
                state, 
                city) as location

If you are 100% sure that the following holds, too (if there are constraints that make it certain):

I will add that if a CITY and/or STATE value exists, then COUNTRY will never be NULL.

then the last 2 parameters can be omitted as well:

select coalesce(city + ', ' + state, 
                city + ', ' + country, 
                state + ', ' + country, 
                country)
Related Question