如何在SQL Server中解析包含URL路径的列并拆分多列？

先模拟一个贴合你场景的测试表，方便演示操作：

CREATE TABLE UrlPaths (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    FullPath NVARCHAR(1000)
);

-- 插入不同长度的测试路径
INSERT INTO UrlPaths (FullPath)
VALUES 
('sites/System1/DocLib1/Folder1/SubFolder/File.pdf'),
('sites/System2/DocLib2/File.docx'),
('sites/System3/DocLib3/FolderA/SubFolderB/File.xlsx');

下面根据你的SQL Server版本，提供两种针对性的解决方案：

方法1：用STRING_SPLIT + PIVOT（SQL Server 2022+ 首选） #

SQL Server 2022给STRING_SPLIT新增了enable_ordinal参数，能直接返回拆分后每个片段的顺序位置，完美解决按顺序转列的需求：

SELECT 
    p.ID,
    [1] AS Segment1, -- 对应sites
    [2] AS Segment2, -- 对应System1
    [3] AS Segment3, -- 对应DocLib1
    [4] AS Segment4, -- 对应Folder1
    [5] AS Segment5, -- 对应SubFolder
    [6] AS Segment6  -- 对应File.pdf
FROM (
    SELECT 
        ID,
        value,
        ordinal
    FROM UrlPaths
    CROSS APPLY STRING_SPLIT(FullPath, '/', 1) -- 最后一个1启用序号返回
) AS s
PIVOT (
    MAX(value) FOR ordinal IN ([1],[2],[3],[4],[5],[6])
) AS p;

如果你的版本是2016-2019（没有enable_ordinal参数），可以用ROW_NUMBER()生成临时序号：

SELECT 
    p.ID,
    [1] AS Segment1,
    [2] AS Segment2,
    [3] AS Segment3,
    [4] AS Segment4,
    [5] AS Segment5,
    [6] AS Segment6
FROM (
    SELECT 
        ID,
        value,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS ordinal
    FROM UrlPaths
    CROSS APPLY STRING_SPLIT(FullPath, '/')
) AS s
PIVOT (
    MAX(value) FOR ordinal IN ([1],[2],[3],[4],[5],[6])
) AS p;

注意：2016-2019的这种写法，极端场景下顺序可能不稳定，对顺序要求极高的话建议用下面的XML方法。

方法2：XML解析法（兼容SQL Server 2008及以上） #

这种方法不依赖新版本函数，兼容性拉满，原理是把路径转成XML节点后按位置提取：

SELECT 
    ID,
    XMLPath.value('/segments[1]/segment[1]', 'NVARCHAR(255)') AS Segment1,
    XMLPath.value('/segments[1]/segment[2]', 'NVARCHAR(255)') AS Segment2,
    XMLPath.value('/segments[1]/segment[3]', 'NVARCHAR(255)') AS Segment3,
    XMLPath.value('/segments[1]/segment[4]', 'NVARCHAR(255)') AS Segment4,
    XMLPath.value('/segments[1]/segment[5]', 'NVARCHAR(255)') AS Segment5,
    XMLPath.value('/segments[1]/segment[6]', 'NVARCHAR(255)') AS Segment6
FROM (
    SELECT 
        ID,
        -- 将路径替换为XML节点格式
        CAST('<segments><segment>' + REPLACE(FullPath, '/', '</segment><segment>') + '</segment></segments>' AS XML) AS XMLPath
    FROM UrlPaths
) AS t;

额外提示 #

• 如果部分路径片段数量不足6个，对应列会返回NULL，可以用ISNULL(XMLPath.value(...), '')替换为空字符串
• 若路径包含<、>、&这类XML特殊字符，转XML前要先转义，避免解析报错

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