11

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?

JoeCool
  • 4,392
  • 11
  • 50
  • 66
  • 2
    `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](http://stackoverflow.com/questions/2916791/sql-server-string-concatenation-with-null) btw – bzlm Feb 10 '11 at 18:11
  • @bzlm 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. – JoeCool Feb 10 '11 at 18:14
  • @bzlm The answer on the "duplicate" question doesn't excite me though. It doesn't address delimiters between the columns. – RichardTheKiwi Feb 10 '11 at 18:18
  • @cyberkiwi Old doesn't mean obsolete on SO. Post your fancy CTE there too. :) – bzlm Feb 10 '11 at 18:19
  • @bzlm CTE is only there to make the query work.. and produce the illustrative output. The query is really the part without the CTE. – RichardTheKiwi Feb 10 '11 at 18:21
  • @cyberkiwi Now you're preaching to the choir. :) – bzlm Feb 10 '11 at 18:22
  • I have no idea what CTE is, but it looks pretty cool. – JoeCool Feb 10 '11 at 18:24

4 Answers4

17

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
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • +1; hadn't considered the possibility of the middle values being null, which seems rather obvious in hindsight. – Adam Robinson Feb 10 '11 at 18:24
  • @Adam God bless 20/20 hindsight! – RichardTheKiwi Feb 10 '11 at 18:27
  • I'm a little confused -- is the stuff above "ignore above this line" necessary for the query to work? – JoeCool Feb 10 '11 at 18:29
  • 3
    @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. – RichardTheKiwi Feb 10 '11 at 18:32
  • This is like magic lol, I've been trying to figure this out for older sql instead of the using the concat_ws function – Andrew Aug 28 '19 at 12:46
5

You can use the Concat function in SQL 2012 and later

SELECT City, State, Country,
Concat(City, ', ', State, ', ', Country) AS 'Location'
FROM Users
cedd
  • 1,741
  • 1
  • 21
  • 34
2

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.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • 2
    More standard? Citation needed. – bzlm Feb 10 '11 at 18:15
  • 1
    @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. – Adam Robinson Feb 10 '11 at 18:17
  • 2
    @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. – RichardTheKiwi Feb 10 '11 at 18:20
  • 3
    Coalesce is in the ISO standard, isnull is SQL Server specific. – HLGEM Feb 10 '11 at 18:21
  • 2
    @bzlm, @cyberwiki: In addition, `COALESCE` is part of ANSI-92 SQL, `ISNULL` is not. – Adam Robinson Feb 10 '11 at 18:22
  • 1
    @adam, @hlgem - while we're devising a Sql Server (TSQL) solution, my comments stand – RichardTheKiwi Feb 10 '11 at 18:24
  • 1
    @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.) – Shannon Severance Feb 11 '11 at 07:02
  • @ShannonSeverance this is old but there's an explanation here: https://dba.stackexchange.com/a/4277/117039 – fujiiface Jun 14 '17 at 21:12
0
;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
Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
Andy Au
  • 1