如何从SQL Server数据库中获得逗号分隔的单个地址，其中一些元素可能为NULL

Question

这是一个非常标准的要求;我有一个保存地址信息的表，我想取出一个"格式化"的地址，所有字段都用逗号分隔符连接。

问题是，一些字段可以是NULL，所以我结束了尾随逗号。例如，一个地址可能是"10 The Strand, London"，末尾没有国家，但下一个地址可能是"5 Fleet Street, London, England"。如果我挑选出每个address元素并假设它总是存在，那么我将把这两个地址表示为:

"舰队街5号，伦敦，"

" The Strand，，， London, England"

从中间去掉额外的逗号很简单，只需要测试NULL。

我知道如何在两个通道中修复尾随逗号问题，无论是使用CTE还是子查询，但是这可以在单个通过数据的通道中完成吗?

下面是一个使用CTE的例子:

DECLARE @Address TABLE (
    Name VARCHAR(255),
    Line1 VARCHAR(255),
    Line2 VARCHAR(255),
    Line3 VARCHAR(255),
    City VARCHAR(255),
    Country VARCHAR(255));
INSERT INTO @Address VALUES ('Complete', 'Test 1', 'Test 2', 'Test 3', 'Oxford', 'England');
INSERT INTO @Address VALUES ('Incomplete', '22 Accacia', NULL, NULL, 'York', 'England');
INSERT INTO @Address VALUES ('Missing End', '10 Bond Street', NULL, NULL, 'London', NULL);
WITH Addresses AS
(
SELECT
    CASE WHEN Name IS NULL THEN '' ELSE Name + ', ' END + 
    CASE WHEN Line1 IS NULL THEN '' ELSE Line1 + ', ' END + 
    CASE WHEN Line2 IS NULL THEN '' ELSE Line2 + ', ' END + 
    CASE WHEN Line3 IS NULL THEN '' ELSE Line3 + ', ' END + 
    CASE WHEN City IS NULL THEN '' ELSE City + ', ' END + 
    CASE WHEN Country IS NULL THEN '' ELSE Country + ', ' END AS [Address]
FROM
    @Address)
SELECT LEFT([Address], LEN([Address]) - 1) AS [Address Clean] FROM Addresses;

这给了我:

Complete, Test 1, Test 2, Test 3, Oxford, England
Incomplete, 22 Accacia, York, England
Missing End, 10 Bond Street, London

为什么我想要这个?部分原因是因为我不能想到一种方法来做到这一点，但"感觉"应该有一种方法来得到我想要的，部分原因是这个查询是运行在一个链接服务器到SQL 2000盒子，所以我不能使用cte(虽然我可以很容易地重写查询使用子查询代替)。

Answer 1

使用coalesce，例如:

WITH Addresses AS
(
SELECT
  coalesce(Name, '') + 
  coalesce(', ' + Line1, '') + 
  coalesce(', ' + Line2, '') + 
  coalesce(', ' + Line3, '') + 
  coalesce(', ' + City, '') + 
  coalesce(', ' + Country, '') AS [Address]
FROM
    @Address)
SELECT Address FROM Addresses

这将返回第一个不为空的参数，因此，例如，如果Line1为空，则将返回"(否则为,Line1)。

注意，要使其工作，CONCAT_NULL_YIELDS_NULL必须设置为ON。

对照测试数据的结果:

Complete, Test 1, Test 2, Test 3, Oxford, England
Incomplete, 22 Accacia, York, England
Missing End, 10 Bond Street, London

Answer 2

我在这里做了几件事。首先，我使用ISNULL来确定值是否为NULL，如果是，则返回''(空字符串)。在测试是否为NULL之前，我将', '作为分隔符添加到值中。这样，如果列为空，那么列+分隔符也是NULL, ISNULL测试仍然返回''。我将分隔符放在列值之前，以便更容易删除额外的分隔符。如果额外的分隔符在字符串的末尾，那么我将不得不使用LEN函数或类似的东西来计算额外的分隔符在哪里。这样，它总是在字符串的开头，通过使用STUFF函数，我可以用''替换前两个字符，有效地删除它们。

DECLARE @Address TABLE (
    Name VARCHAR(255),
    Line1 VARCHAR(255),
    Line2 VARCHAR(255),
    Line3 VARCHAR(255),
    City VARCHAR(255),
    Country VARCHAR(255));
INSERT INTO @Address VALUES ('Complete', 'Test 1', 'Test 2', 'Test 3', 'Oxford', 'England');
INSERT INTO @Address VALUES ('Incomplete', '22 Accacia', NULL, NULL, 'York', 'England');
INSERT INTO @Address VALUES ('Missing End', '10 Bond Street', NULL, NULL, 'London', NULL);
SELECT STUFF(
        ISNULL(', '+Name,'') + ISNULL(', '+Line1,'') + ISNULL(', '+Line2,'') + 
        ISNULL(', '+Line3,'') + ISNULL(', '+City,'') + ISNULL(', '+Country,'')
    ,1,2,'')
FROM @Address