如何在SQL中筛选特定逗号分隔符并拆分字符串

源数据 #

预期输出 #

实现步骤 #

1. 创建自定义拆分函数：该函数会跟踪括号层级，仅拆分不在括号内的逗号。

CREATE FUNCTION dbo.SplitIgnoringBrackets
(
    @InputString NVARCHAR(MAX),
    @Delimiter CHAR(1)
)
RETURNS @Results TABLE
(
    Item NVARCHAR(MAX),
    ItemOrder INT
)
AS
BEGIN
    DECLARE @StartIndex INT = 1,
            @EndIndex INT,
            @BracketCount INT = 0,
            @Order INT = 1;

    WHILE @StartIndex <= LEN(@InputString)
    BEGIN
        SET @EndIndex = @StartIndex;
        SET @BracketCount = 0;

        WHILE @EndIndex <= LEN(@InputString)
        BEGIN
            IF SUBSTRING(@InputString, @EndIndex, 1) = '(' 
                SET @BracketCount += 1;
            ELSE IF SUBSTRING(@InputString, @EndIndex, 1) = ')' 
                SET @BracketCount -= 1;
            
            IF SUBSTRING(@InputString, @EndIndex, 1) = @Delimiter AND @BracketCount = 0
                BREAK;

            SET @EndIndex += 1;
        END

        INSERT INTO @Results (Item, ItemOrder)
        VALUES (TRIM(SUBSTRING(@InputString, @StartIndex, @EndIndex - @StartIndex)), @Order);

        SET @Order += 1;
        SET @StartIndex = @EndIndex + 1;
    END

    RETURN;
END;

2. 使用函数+PIVOT转换为多列：通过CTE拆分数据，再用PIVOT将行转成预期的列。

WITH SplitData AS (
    SELECT 
        t.[left],
        t.[center],
        sf.Item,
        'c' + CAST(ROW_NUMBER() OVER (PARTITION BY t.[left], t.[center] ORDER BY sf.ItemOrder) AS VARCHAR(10)) AS ColumnName
    FROM YourTableName t  -- 替换为实际表名
    CROSS APPLY dbo.SplitIgnoringBrackets(t.[right], ',') sf
)
SELECT 
    [left],
    [center],
    ISNULL(c1, '') AS c1,
    ISNULL(c2, '') AS c2,
    ISNULL(c3, '') AS c3,
    ISNULL(c4, '') AS c4
FROM SplitData
PIVOT (
    MAX(Item)
    FOR ColumnName IN (c1, c2, c3, c4)
) p;

注意事项 #

• 函数默认处理成对的括号，若存在未闭合的括号可能导致结果异常；
• 如果需要支持更多列（如c5、c6），只需在PIVOT的IN子句中添加对应列名即可；
• 替换代码中的YourTableName为实际的表名称。

内容的提问来源于stack exchange，提问作者Salva