T-SQL: Best way to handle NULL values in string concatenation

Question

If in a SELECT statement I'm selecting a concatenated string that uses values from the table(s) I'm selecting from, what's the best way to handle NULLs for those values so that I still have my string? As in, if I'm selecting City, State, and Country for a User, and I want a third field that concatenates them all:

SELECT City, State, Country,
City + ', ' + State + ', ' + Country AS 'Location'
FROM Users

However, 'Location' is NULL if any of the three fields is NULL (which is happens whenever the user is not from the US).

My current solution is this:

SELECT City, State, Country,
City + ', ' + COALESCE(State + ', ', '') + Country AS 'Location'
FROM Users

But I wasn't sure if this was just a hack and if there's a much better way to do it. Thoughts?

Answer 1

You can use the Concat function in SQL 2012 and later

SELECT City, State, Country,
Concat(City, ', ', State, ', ', Country) AS 'Location'
FROM Users

Answer 2

To predictably look correct with commas between every two fields, you can use this form

;with users(City, State, Country) as (
select 'a', null, 'c' union all
select 'a', 'b', 'c' union all
select null, null, 'c')

-- ignore above this line
SELECT City, State, Country,
    STUFF(
        ISNULL(', ' + City, '')+
        ISNULL(', ' + State, '')+
        ISNULL(', ' + Country, ''), 1, 2, '') AS 'Location'
FROM Users

Output

City State Country Location
---- ----- ------- --------
a    NULL  c       a, c
a    b     c       a, b, c
NULL NULL  c       c