How to concatenate columns properly using T-SQL?

Question

I have a address in more than one column in a table.

SELECT FirstName, LastName, StreetAddress, City, Country, PostalCode 
FROM Client

I am trying to concatenate address related columns into one filed using Comma (,) as a separator but if any of the column "eg. City" is null or empty, comma should not be there.

How to use ternary operator in TSQL like one has in c#? Or suggest me the best practice?

Thanks

Oliverolivera

Answer 1

When you concatenate anything with a null, it returns null. So I'm trying to concatenate a comma with the given column value and if that expression returns null, I use Coalesce to return an empty string. At the end, if I get a value, the entire result will start with a comma. So I remove that comma using the Stuff function.

Select Stuff(
    Coalesce(',' + FirstName,'')
    + Coalesce(',' + LastName,'')
    + Coalesce(',' + StreetAddress,'')
    + Coalesce(',' + City,'')
    + Coalesce(',' + Country,'')
    + Coalesce(',' + PostalCode ,'')
    , 1, 1, '')
From Client

If you only want the address, then obviously you would only include those columns:

Select FirstName, LastName
    , Stuff(
        Coalesce(',' + StreetAddress,'')
        + Coalesce(',' + City,'')
        + Coalesce(',' + Country,'')
        + Coalesce(',' + PostalCode ,'')
    , 1, 1, '')
From Client

Shapeless

+1, again something learned :) But you should remove the first-/lastname from that because i think the OP only wants to concatenate the address. – Electrokinetic

Answer 2

If you include the NULLIF function inside the COALESCE function it will work correctly for empty columns as well as NULL columns

SELECT FirstName,
       LastName,
       STUFF(
           COALESCE(',' + NULLIF(StreetAddress, ''), '')  + 
           COALESCE(',' + NULLIF(City, ''), '') +
           COALESCE(',' + NULLIF(Country, ''), '') +
           COALESCE(',' + NULLIF(PostalCode , ''), ''),
           1, 1, '') AS "Address"
   FROM Client

Unprofessional

NULLIF, cool! Then you could add a LTRIM and RTRIM to remove any whitespace to get COALESCE(',' + NULLIF(RTRIM(LTRIM(StreetAddress)), ''), '') – Vedavedalia

Answer 3

Look at isnull

You can also look at using COALESCE function, please look it up in BOL:

Returns the first nonnull expression among its arguments.

Finally another thing you could do is use a CASE function.

SELECT Address1, CASE Address2 IS NOT NULL THEN "," + Address2 ELSE...

Profound

SELECT City1 + ISNULL(City2)? "":","City2 is definitely not valid TSQL. – Fortuitism

@Martin - Sorry that was SSIS. But the rest should be valid. – Profound

Answer 4

This is an old question and SQL 2012 added the CONCAT function that made this sort of thing a bit easier. Try this:

Select FirstName, LastName, 
    Stuff(
        Concat(
        ',' + FirstName,
        ',' + LastName,
        ',' + StreetAddress,
        ',' + City,
        ',' + Country,
        ',' + PostalCode ),
    1,1,'')
From Client

CONCAT will automatically treat NULL as empty string so you dont need to use ISNULL or COALESCE. The STUFF function is there to remove the first comma, as in the answer by Thomas

Mallorie

Answer 5

You could use Case-Expression.

create table #Client(
FirstName varchar(20), 
LastName varchar(50), 
StreetAddress varchar(50), 
City varchar(20), 
Country varchar(20), 
PostalCode varchar(20)
)
insert into #Client(FirstName,LastName,StreetAddress,City,Country,PostalCode)values('Jeff','Bridges','Street1','City1','US','12345')
insert into #Client(FirstName,LastName,StreetAddress,City,Country,PostalCode)values('Steven','Baldwin','Street2','City2','US','12345')
insert into #Client(FirstName,LastName,StreetAddress,City,Country,PostalCode)values('Barack','Obama','Street3',NULL,'US','12345')
insert into #Client(FirstName,LastName,StreetAddress,City,Country,PostalCode)values('Nora','Jones','Street4','City4','US','12345')

SELECT FirstName, LastName,
(CASE WHEN City IS NULL AND StreetAddress IS NULL THEN '' 
      WHEN City IS Null AND StreetAddress IS NOT NULL Then StreetAddress 
      WHEN City IS NOT Null AND StreetAddress IS NOT NULL THEN StreetAddress + ',' + City END
)AS Adress, Country, PostalCode 
FROM #Client

drop table #Client

Electrokinetic

@Thanks. But adding all found columns (StreetAddress, City, Country, PostalCode) are really hard for me.. ?? How I gonna use this CASE statement? – Oliverolivera

@User: use Thomas solution – Electrokinetic

Answer 6

Another solution is to use ISNULL

Select FirstName, LastName
    , ISNULL(StreetAddress+', ','')
      +ISNULL(City+', ','')
      +ISNULL(Country+', ','')
      +ISNULL(PostalCode,'')
FROM Client

If a value is null, then the concatenation result will be null. ISNULL will replace the first expression with the second expression.

http://msdn.microsoft.com/en-us/library/ms184325(v=SQL.90).aspx

Jeuz

Answer 7

CONCAT_WS is the function you want which will add together multiple columns with a specified separator. If any of the values are null they will not be included and nor will the separator.

Example:

SELECT CONCAT_WS(', ', 'First', NULL, 'Third', 'Fourth');

Will return:

First, Third, Fourth

See more: https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql

Jeuz