How to concatenate columns properly using T-SQL?

问题:

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

回答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

回答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

回答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...

回答4:

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

回答5:

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

回答6:

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