SQL Server中拆分斜杠分隔列值并对应输出的实现方法

嘿，我来帮你搞定这个拆分对应列值的需求！下面是两种适用于SQL Server的方案，根据你的数据库版本选择就行：

如果你用的是SQL Server 2016及以上版本，STRING_SPLIT能快速拆分字符串，但默认不会返回元素的位置顺序。所以我们需要给原表每行分配唯一标识，再给拆分后的元素加行号，最后通过这两个值关联Col1和Col2的对应元素。

示例代码：

-- 假设你的表名为YourTable
WITH CTE_Col1 AS (
    SELECT 
        RowId = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), -- 给原表每行分配唯一ID
        Col1_Value = value,
        ElementIndex = ROW_NUMBER() OVER (PARTITION BY RowId ORDER BY (SELECT NULL)) -- 拆分后元素的位置序号
    FROM YourTable
    CROSS APPLY STRING_SPLIT(Col1, '/')
),
CTE_Col2 AS (
    SELECT 
        RowId = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        Col2_Value = value,
        ElementIndex = ROW_NUMBER() OVER (PARTITION BY RowId ORDER BY (SELECT NULL))
    FROM YourTable
    CROSS APPLY STRING_SPLIT(Col2, '/')
)
SELECT 
    c1.Col1_Value AS Col1,
    c2.Col2_Value AS Col2
FROM CTE_Col1 c1
JOIN CTE_Col2 c2 ON c1.RowId = c2.RowId AND c1.ElementIndex = c2.ElementIndex
ORDER BY c1.RowId, c1.ElementIndex;

额外提示：SQL Server 2022及以上版本可以直接用STRING_SPLIT的第三个参数enable_ordinal来获取元素位置，不用手动生成行号，代码会更简洁：STRING_SPLIT(Col1, '/', 1)，返回结果里会多一个ordinal列表示元素的顺序位置。

如果你的数据库版本低于2016，没有STRING_SPLIT函数，可以自己写一个表值函数，拆分字符串的同时返回元素的位置：

先创建拆分函数：

CREATE FUNCTION dbo.SplitStringWithPosition
(
    @InputStr NVARCHAR(MAX),
    @Delimiter NVARCHAR(5)
)
RETURNS @ResultTable TABLE (Element NVARCHAR(MAX), Position INT)
AS
BEGIN
    DECLARE @CurrentPos INT = 1;
    DECLARE @StartIdx INT = 1;
    DECLARE @EndIdx INT;

    WHILE CHARINDEX(@Delimiter, @InputStr, @StartIdx) > 0
    BEGIN
        SET @EndIdx = CHARINDEX(@Delimiter, @InputStr, @StartIdx);
        INSERT INTO @ResultTable (Element, Position)
        VALUES (SUBSTRING(@InputStr, @StartIdx, @EndIdx - @StartIdx), @CurrentPos);
        
        SET @StartIdx = @EndIdx + LEN(@Delimiter);
        SET @CurrentPos = @CurrentPos + 1;
    END

    -- 插入最后一个分隔后的元素
    INSERT INTO @ResultTable (Element, Position)
    VALUES (SUBSTRING(@InputStr, @StartIdx, LEN(@InputStr) - @StartIdx + 1), @CurrentPos);

    RETURN;
END
GO

然后用这个函数拆分并关联数据：

WITH CTE_Col1 AS (
    SELECT 
        RowId = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        Col1_Value = Element,
        Position = Position
    FROM YourTable
    CROSS APPLY dbo.SplitStringWithPosition(Col1, '/')
),
CTE_Col2 AS (
    SELECT 
        RowId = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        Col2_Value = Element,
        Position = Position
    FROM YourTable
    CROSS APPLY dbo.SplitStringWithPosition(Col2, '/')
)
SELECT 
    c1.Col1_Value AS Col1,
    c2.Col2_Value AS Col2
FROM CTE_Col1 c1
JOIN CTE_Col2 c2 ON c1.RowId = c2.RowId AND c1.Position = c2.Position
ORDER BY c1.RowId, c1.Position;

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