SQL Server 2019拆分URL为多列的高效实现方案问询

针对你需要拆分多级域名到独立列的需求，结合SQL Server 2019的特性，推荐使用**STRING_SPLIT带序号参数+PIVOT行转列**的方案，既解决长域名限制，又能高效处理大规模数据。

核心思路 #

1. 利用SQL Server 2019新增的STRING_SPLIT第三个参数（1）启用序号返回，确保拆分后的域名部分顺序可追溯
2. 对拆分后的序号进行反转，让顶级域名（如com）对应第一列，依次向下映射子域名
3. 通过PIVOT将行格式的域名部分转成列格式，支持任意长度的域名层级

静态列方案（推荐，性能更优） #

适合预先知道业务中最长域名层级的场景，比如最多6级域名：

1. 创建测试数据

CREATE TABLE #DomainTest (ID INT IDENTITY(1,1), DomainURL NVARCHAR(255))
INSERT INTO #DomainTest (DomainURL)
VALUES 
('store.example.com'),
('demo.m.en.store.example.com'),
('test.sub.subsub.example.co.uk')

2. 拆分并转列

WITH DomainParts AS (
    SELECT 
        dt.ID,
        dt.DomainURL,
        s.value AS DomainPart,
        -- 反转序号，让顶级域名排在第1位
        ROW_NUMBER() OVER (PARTITION BY dt.ID ORDER BY s.ordinal DESC) AS ReverseOrdinal
    FROM #DomainTest dt
    -- 启用ordinal参数，确保拆分顺序稳定
    CROSS APPLY STRING_SPLIT(dt.DomainURL, '.', 1) s
)
SELECT 
    ID,
    DomainURL,
    [1] AS 顶级域名,    -- 对应com/co.uk等
    [2] AS 二级域名,    -- 对应example
    [3] AS 三级域名,    -- 对应store
    [4] AS 四级域名,    -- 对应en
    [5] AS 五级域名,    -- 对应m
    [6] AS 六级域名     -- 对应demo
FROM DomainParts
PIVOT (
    MAX(DomainPart)
    FOR ReverseOrdinal IN ([1],[2],[3],[4],[5],[6])
) p
ORDER BY ID

动态列方案（灵活适配任意层级） #

如果域名层级不固定，可通过动态SQL自动生成对应列：

DECLARE @MaxLevels INT, @PivotColumns NVARCHAR(MAX), @SQL NVARCHAR(MAX)

-- 获取当前数据中的最大域名层级
SELECT @MaxLevels = MAX((SELECT COUNT(*) FROM STRING_SPLIT(DomainURL, '.', 1))) 
FROM #DomainTest

-- 生成PIVOT所需的列名列表
SET @PivotColumns = STRING_AGG(QUOTENAME(n), ',')
FROM (SELECT TOP(@MaxLevels) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n FROM sys.all_columns) t

-- 构建并执行动态SQL
SET @SQL = N'
WITH DomainParts AS (
    SELECT 
        dt.ID,
        dt.DomainURL,
        s.value AS DomainPart,
        ROW_NUMBER() OVER (PARTITION BY dt.ID ORDER BY s.ordinal DESC) AS ReverseOrdinal
    FROM #DomainTest dt
    CROSS APPLY STRING_SPLIT(dt.DomainURL, ''.'', 1) s
)
SELECT 
    ID,
    DomainURL,
    ' + @PivotColumns + N'
FROM DomainParts
PIVOT (
    MAX(DomainPart)
    FOR ReverseOrdinal IN (' + @PivotColumns + N')
) p
ORDER BY ID'

EXEC sp_executesql @SQL

方案优势对比 #

• 替代REVERSE/CHARINDEX嵌套：避免代码冗余，原生函数性能更优，适合大规模数据
• 解决STRING_SPLIT无序号问题：启用ordinal参数后，拆分顺序完全可控，确保PIVOT映射正确
• 突破PARSENAME4级限制：支持任意长度的域名层级，无数量上限

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