在SQL Server中将指定分隔字符串转换为结构化列的需求

方法1：使用STRING_SPLIT + PIVOT（适用于SQL Server 2016及以上版本） #

利用STRING_SPLIT函数拆分字符串，结合行号标记拆分项的位置，再通过PIVOT将行数据转换为指定列：

DECLARE @InputStr NVARCHAR(100) = 'H|A|100|200|300|400|500';

SELECT 
    [1] AS [From],
    [2] AS [To],
    [3] AS [FromID],
    [4] AS [ToID],
    [5] AS [Distance1],
    [6] AS [Distance2],
    [7] AS [Distance3]
FROM (
    SELECT 
        value,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ItemIndex
    FROM STRING_SPLIT(@InputStr, '|')
) AS SplitData
PIVOT (
    MAX(value)
    FOR ItemIndex IN ([1], [2], [3], [4], [5], [6], [7])
) AS PivotResult;

方法2：使用字符串截取函数（兼容低版本SQL Server） #

如果你的SQL Server版本不支持STRING_SPLIT，可以通过嵌套CHARINDEX定位分隔符位置，再用SUBSTRING截取对应字段：

DECLARE @InputStr NVARCHAR(100) = 'H|A|100|200|300|400|500';

SELECT
    -- 截取第一个字段
    SUBSTRING(@InputStr, 1, CHARINDEX('|', @InputStr) - 1) AS [From],
    -- 截取第二个字段
    SUBSTRING(@InputStr, CHARINDEX('|', @InputStr) + 1, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr) + 1) - CHARINDEX('|', @InputStr) - 1) AS [To],
    -- 截取第三个字段
    SUBSTRING(@InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr) + 1) + 1, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr) + 1) + 1) - CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr) + 1) - 1) AS [FromID],
    -- 截取第四个字段
    SUBSTRING(@InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr) + 1) + 1) + 1, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr) + 1) + 1) + 1) - CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr) + 1) + 1) - 1) AS [ToID],
    -- 截取第五个字段
    SUBSTRING(@InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr) + 1) + 1) + 1) + 1, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr) + 1) + 1) + 1) + 1) - CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr) + 1) + 1) + 1) - 1) AS [Distance1],
    -- 截取第六个字段
    SUBSTRING(@InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr) + 1) + 1) + 1) + 1) + 1, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr) + 1) + 1) + 1) + 1) + 1) - CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr, CHARINDEX('|', @InputStr) + 1) + 1) + 1) + 1) - 1) AS [Distance2],
    -- 截取最后一个字段
    RIGHT(@InputStr, CHARINDEX('|', REVERSE(@InputStr)) - 1) AS [Distance3];

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