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?

Sp

coalesce is a standard practice for this. In your case you'd only need isnull though. possible duplicate of SQL Server String Concatenation with Null btw – Weismann

@Weismann Oops! You're right. Not sure why it didn't turn up when I searched or entered the title. Perhaps because I specifically put "t-sql." Voting to close. It's always a little weird to vote to close your own question. It tickles. – Sp

@Weismann The answer on the "duplicate" question doesn't excite me though. It doesn't address delimiters between the columns. – Consideration

@cyberkiwi Old doesn't mean obsolete on SO. Post your fancy CTE there too. :) – Weismann

@Weismann CTE is only there to make the query work.. and produce the illustrative output. The query is really the part without the CTE. – Consideration

@cyberkiwi Now you're preaching to the choir. :) – Weismann

I have no idea what CTE is, but it looks pretty cool. – Sp

Answer 1

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

Consideration

+1; hadn't considered the possibility of the middle values being null, which seems rather obvious in hindsight. – Crabbe

@Adam God bless 20/20 hindsight! – Consideration

I'm a little confused -- is the stuff above "ignore above this line" necessary for the query to work? – Sp

@Joe CTE (the WITH) clause allows you to virtually define a table (or multiple) for use in the immediately following query (SELECT). You don't need it, just use the query after the --ignore line. I use it to define a table for the SELECT query instead of creating a temp table. – Consideration

This is like magic lol, I've been trying to figure this out for older sql instead of the using the concat_ws function – Venusian

Answer 2

You can use the Concat function in SQL 2012 and later

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

Fulgurate

That's the one I'd use unless version of the database was earlier that 2012. – Sayyid

Answer 3

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.

Stancil

More standard? Citation needed. – Weismann

@bzlm: While I wouldn't go so far as to say it's "more standard" (in terms of adoption rate), but it's generally the preferred syntax for new development, given that it covers all cases that ISNULL covers, as well as others (chaining additional options that would have required multiple ISNULL statements, for example). Additionally, COALESCE is available on SqlCE, whereas ISNULL is not. – Crabbe

@Adam, @Bzlm unless we're dealing with SQLCE, no point dragging it out. Btw, COALESCE has some dynamic typing and performance issues when compared to IsNull, so if you are on SQL Server (non-CE), and only need 2 values, don't venture into COALESCE unnecessarily. – Consideration

Coalesce is in the ISO standard, isnull is SQL Server specific. – Stancil

@bzlm, @cyberwiki: In addition, COALESCE is part of ANSI-92 SQL, ISNULL is not. – Crabbe

@adam, @hlgem - while we're devising a Sql Server (TSQL) solution, my comments stand – Consideration

@cyberkiwi: What do you mean by dynamic typing issues? My experience is that coalesce normally does what I want, isnull maybe, maybe not. (Actually coalesce has always picked the type I wanted. There have been some questions on SO where it was the wrong choice.) – Gonagle

@ShannonSeverance this is old but there's an explanation here: dba.stackexchange.com/a/4277/117039 – Incubate

Answer 4

;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

Havildar