SQL Server：字符串拆分与连接

• 下载 ListToString_StringToList
• 下载 row_to_rows

引言

String 拆分和连接在大多数编程语言（如 C#、JAVA、PHP）中都很常见。但我没有在 SQL SERVER 中找到任何选项。让我们创建一些代码在 SQL Server 数据库中实现相同的功能。

在使用 SQL Server 中的 string 对象时，了解一些函数（如 CONVERT、STUFF、CHARINDEX、SUBSTRING、LTRIM、RTRIM、XML PATH()）始终是好的。

连接：列表到字符串列表

这里，我们将把 (1), (2), (3), (4), (NULL) 表值转换为 NULL,1,2,3,4 字符串。

DECLARE @tblId TABLE (Id BIGINT NULL);
INSERT INTO @tblId VALUES (1), (2), (3), (4), (NULL);
SELECT * FROM @tblId;

DECLARE @separator NVARCHAR(10) = ',';
SELECT STUFF((SELECT @separator + COALESCE(CONVERT(NVARCHAR(MAX), Id), 'NULL') 
              FROM @tblId        /*to deselect NULL, add WHERE condition*/
              ORDER BY Id
              FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)')
              , 1, 1, '') AS Ids;

用法类似于聚合函数

DECLARE @tblUserGroup TABLE (GroupId INT, UserId INT NULL);
INSERT INTO @tblUserGroup VALUES
(1, 1), (1, 2), (1, 3),
(2, 4), (2, 5), (2, 6),
(3, NULL);
SELECT * FROM @tblUserGroup;

DECLARE @separator CHAR = ',';
SELECT 
    GroupId, 
    COUNT(UserId) AS TotalUser,
    STUFF((SELECT @separator + CONVERT(NVARCHAR(MAX), UserId) 
            FROM @tblUserGroup AS uc
            WHERE uc.GroupId = g.GroupId        /*to deselect NULL, add WHERE condition*/
            ORDER BY UserId
            FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)')
            , 1, 1, '') AS UserIds
FROM @tblUserGroup AS g
GROUP BY GroupId

使用用户定义类型创建函数是另一种选择，但同样，我们需要声明变量表。但如果我们能创建 AGGREGATE 函数或 CLR，那就更好了。

拆分：字符串列表到列表

此功能在 SQL Server 2016 及更高版本中默认设置为 STRING_SPLIT https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017。

但对于旧版本，我们可以这样做

DECLARE @valueList NVARCHAR(MAX) = 'Dan, Han, Mos, Ben,, ,null, Null, NULL';
DECLARE @separator NVARCHAR(10) = ',';

/*split process*/
DECLARE @tblValue TABLE(Value NVARCHAR(MAX));
DECLARE @fromIndex INT, @seperatiorAtIndex INT, @value NVARCHAR(100);
SET @fromIndex = 1;
SET @seperatiorAtIndex = CHARINDEX(@separator, @valueList)

WHILE @fromIndex < LEN(@valueList) + 1
BEGIN
    IF @seperatiorAtIndex = 0  
        SET @seperatiorAtIndex = LEN(@valueList) + 1
    SET @value = LTRIM(RTRIM(SUBSTRING_
                 (@valueList, @fromIndex, @seperatiorAtIndex - @fromIndex))); 
   
    INSERT INTO @tblValue VALUES(@value) -- inserting to the table  
   
    SET @fromIndex = @seperatiorAtIndex + 1
    SET @seperatiorAtIndex = CHARINDEX(@separator, @valueList, @fromIndex)  
END

结果

/*result*/
SELECT * FROM @tblValue;
SELECT Value FROM @tblValue;

排除 null 或空值

SELECT * FROM @tblValue
WHERE LTRIM(RTRIM(UPPER(Value))) NOT IN ('', 'NULL');    /*exclude null or empty*/

创建表值函数

这里，我们将创建一个辅助函数

/*create table-valued function*/
CREATE FUNCTION fnSplit(@valueList NVARCHAR(MAX), @separator NVARCHAR(10))
RETURNS @tblValue TABLE(Value NVARCHAR(MAX))
AS 
BEGIN
    /*split process*/
    DECLARE @fromIndex INT, @seperatiorAtIndex INT, @value NVARCHAR(100);
    SET @fromIndex = 1;
    SET @seperatiorAtIndex = CHARINDEX(@separator, @valueList)

    WHILE @fromIndex < LEN(@valueList) + 1
    BEGIN
           IF @seperatiorAtIndex = 0  
               SET @seperatiorAtIndex = LEN(@valueList) + 1
           SET @value = LTRIM(RTRIM(SUBSTRING_
               (@valueList, @fromIndex, @seperatiorAtIndex - @fromIndex))); 
  
           INSERT INTO @tblValue VALUES(@value) -- inserting to the table  
   
           SET @fromIndex = @seperatiorAtIndex + 1
           SET @seperatiorAtIndex = CHARINDEX(@separator, @valueList, @fromIndex)  
    END

    RETURN
END;

像这样使用此辅助函数

/*using that function*/
DECLARE @valueList NVARCHAR(MAX) = 'Dan, Han, Mos, Ben';
DECLARE @separator NVARCHAR(10) = ',';
SELECT Value FROM fnSplit(@valueList, @separator);

请在附件中找到必要的 SQL 文件。与其一次性运行整个脚本，不如分段运行。

拆分：单行到多行

这里，我们拆分 Roles 列的值，并从单行生成多行。

DECLARE @tblSettings TABLE (
    Id INT IDENTITY(1, 1),
    StoreId INT,
    Roles VARCHAR(MAX) NULL
);
INSERT INTO @tblSettings (StoreId, [Roles])
VALUES
(10, 'A, B,C , , D ,'),
(20, ''),
(30, NULL);
SELECT * FROM @tblSettings;

WITH
RoleNamesSettingSplitted(Id, StoreId, RoleName, RemainingRoleNames) AS
(
    /*recursion, may need to use OPTION(MAXRECURSION 0) at client*/
    SELECT
        Id,
        StoreId,
        LTRIM(RTRIM(CAST(LEFT(Roles, CHARINDEX(',', Roles + ',') - 1) AS VARCHAR(MAX)))),
        CAST (STUFF(Roles, 1, CHARINDEX(',', Roles + ','), '') AS VARCHAR(MAX))
    FROM @tblSettings 
    WHERE Roles IS NOT NULL AND LTRIM(RTRIM(Roles)) <> ''
    UNION ALL
    SELECT
        Id,
        StoreId,
        LTRIM(RTRIM(CAST(LEFT(RemainingRoleNames, _
              CHARINDEX(',', RemainingRoleNames + ',') - 1) AS VARCHAR(MAX)))),
        CAST (STUFF(RemainingRoleNames, 1, _
              CHARINDEX(',', RemainingRoleNames + ','), '') AS VARCHAR(MAX))
    FROM RoleNamesSettingSplitted
    WHERE LTRIM(RTRIM(RemainingRoleNames)) > ''
)
SELECT 
    Id,
    StoreId,
    RoleName
FROM RoleNamesSettingSplitted
WHERE LEN(RoleName) > 0                /*ignore empty values*/
GROUP BY Id, StoreId, RoleName        /*ignore duplicates*/
--OPTION(MAXRECURSION 0)

此查询正在进行递归调用。根据列表大小，我们可能需要在查询中包含 OPTION(MAXRECURSION 0)。

历史

• 2019年2月19日：初始版本
• 2023年6月23日：第二版本