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

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, 'Lo开发者_C百科cation' 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?

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

You can use the Concat function in SQL 2012 and later

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

Yes that is the way to go. You could also use isnull() but coalesce is more standard. You might consider if you might have nulls in city or country as well. You also might consider that users may have multipel addresses and may have more than one city, state, country and perhaps a related table would be better for this information.

;WITH 

users(City, State, Country) AS (
  SELECT    'a',
            null,
            'c' 
  UNION     ALL
  SELECT    'a',
            'b', 
            'c' 
  UNION     ALL
  SELECT    null,
            null, 
            'c'
)

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