SQL Server 2017：如何从字符串列提取指定字段为独立业务列？

下面提供两种兼容不同SQL Server版本的解决方案，可精准提取目标列的数值：

方法1：基于原有XML拆分逻辑优化（兼容多数SQL Server版本） #

SELECT 
    A.*,
    [Number of reservations] = TRIM(SUBSTRING(xDim.value('/x[1]', 'varchar(100)'), CHARINDEX(':', xDim.value('/x[1]', 'varchar(100)')) + 1, 100)),
    [Perf ID] = CASE 
                    WHEN xDim.value('/x[2]', 'varchar(100)') IS NOT NULL 
                    THEN TRIM(SUBSTRING(
                                xDim.value('/x[2]', 'varchar(100)'), 
                                LEN(xDim.value('/x[2]', 'varchar(100)')) - CHARINDEX(':', REVERSE(xDim.value('/x[2]', 'varchar(100)'))) + 2, 
                                100
                            ))
                    ELSE NULL 
                END
FROM Test A
CROSS APPLY (VALUES (CONVERT(xml, '<x>' + REPLACE(A.resource_type, '¶', '</x><x>') + '</x>'))) B(xDim)

逻辑说明： #

• 提取预约数：针对拆分后的第一部分字符串，定位第一个冒号的位置，截取冒号后的内容并去除前后空格，得到纯数字值。
• 提取Perf ID：先判断拆分后的第二部分是否存在，若存在则通过反转字符串定位原字符串最后一个冒号的位置，截取冒号后的内容并去空格；不存在则返回NULL。

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

如果你的环境支持STRING_SPLIT函数，可以用更直观的行列转换方式实现：

SELECT 
    resource_type,
    [Number of reservations] = TRIM(SUBSTRING([Number of reservations], CHARINDEX(':', [Number of reservations]) + 1, 100)),
    [Perf ID] = CASE 
                    WHEN [Perf ID] IS NOT NULL 
                    THEN TRIM(SUBSTRING([Perf ID], LEN([Perf ID]) - CHARINDEX(':', REVERSE([Perf ID])) + 2, 100))
                    ELSE NULL 
                END
FROM (
    SELECT 
        A.resource_type,
        s.value,
        -- 标记每个拆分部分的键名
        key_type = CASE 
                      WHEN s.value LIKE 'Number of reservations:%' THEN 'Number of reservations'
                      WHEN s.value LIKE 'Perf ID:%' THEN 'Perf ID'
                   END
    FROM Test A
    CROSS APPLY STRING_SPLIT(A.resource_type, '¶') s
) src
PIVOT (
    MAX(value) FOR key_type IN ([Number of reservations], [Perf ID])
) pvt

逻辑说明： #

1. 用STRING_SPLIT按¶拆分每个字符串，标记每个部分对应的键名（预约数或Perf ID）。
2. 通过PIVOT将行数据转换为列结构。
3. 对转换后的列分别提取冒号后的目标数值，空值返回NULL。

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