如何使用 T-SQL 正确连接列? [英] 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

我正在尝试使用逗号 (,) 作为分隔符将与地址相关的列连接到一个字段中，但如果例如城市"列中的任何列为 null 或为空，则逗号不应该存在.

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.

如何像在 C# 中那样在 TSQL 中使用三元运算符?或者建议我最好的做法?

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

谢谢

推荐答案

当你用 null 连接任何东西时，它返回 null.所以我试图将一个逗号与给定的列值连接起来，如果该表达式返回 null，我使用 Coalesce 返回一个空字符串.最后，如果我得到一个值，整个结果将以逗号开头.所以我使用 Stuff 函数删除了那个逗号.

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

这篇关于如何使用 T-SQL 正确连接列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！