SQL中如何使用PATINDEX与SUBSTRING解析字符串提取指定值

问题场景 #

现有如下格式的字符串需要提取固定规则的字段值：

declare @test1 varchar(max) = 'Month 05/2022, Ord195506 Cst373175'
declare @test2 varchar(max) = 'Month 05/2022, Ord195506 Cst373175, something...'

-- 原有固定长度提取写法，无法适配可变长度数字
select SUBSTRING(@test, PATINDEX('%Ord[0-9][0-9][0-9][0-9][0-9][0-9]%', @test1) + 3, 6)

需要提取的目标值：

• 月份字段：05/2022
• Ord/Abo前缀后的数字：长度范围1位到任意长度，示例值为195506
• Cst前缀后的数字：示例值为373175

要求避免大量嵌套PATINDEX、SUBSTRING、RIGHT这类位置计算函数完成提取。

最简实现方案（SQL Server 2017及以上版本） #

不需要手动计算字符位置，通过统一分隔符+拆分匹配的逻辑即可实现，代码可读性高，适配任意长度的后缀数字：

-- 以@test1为例，@test2、含Abo标识的字符串逻辑完全一致
SELECT
    -- 提取MM/YYYY格式月份
    MAX(CASE WHEN value LIKE '[0-9][0-9]/[0-9][0-9][0-9][0-9]' THEN value END) AS MonthVal,
    -- 提取Ord后数字，适配任意长度
    MAX(CASE WHEN value LIKE 'Ord[0-9]%' THEN REPLACE(value, 'Ord', '') END) AS OrdVal,
    -- 提取Abo后数字，按需新增即可
    -- MAX(CASE WHEN value LIKE 'Abo[0-9]%' THEN REPLACE(value, 'Abo', '') END) AS AboVal,
    -- 提取Cst后数字
    MAX(CASE WHEN value LIKE 'Cst[0-9]%' THEN REPLACE(value, 'Cst', '') END) AS CstVal
FROM STRING_SPLIT(REPLACE(@test1, ',', ' '), ' ')
WHERE value <> ''

逻辑说明 #

1. 先通过REPLACE把字符串中所有逗号替换为空格，统一分隔符，避免拆分时出现带逗号的冗余片段
2. 用STRING_SPLIT按空格把整串拆分为独立的文本片段，全程不需要手动计算各标识的起始、结束位置
3. 最后通过CASE语句匹配对应规则的片段，直接删除前缀拿到目标值，无论后缀数字是1位还是上百位都能正常提取，字符串末尾的无关内容（比如示例中的something...）会因为不匹配规则被自动忽略。

低版本兼容方案（SQL Server 2016及以下） #

如果环境没有STRING_SPLIT函数，可以用JSON函数实现相同的拆分逻辑，同样不需要嵌套多层位置函数：

SELECT
    MAX(CASE WHEN value LIKE '[0-9][0-9]/[0-9][0-9][0-9][0-9]' THEN value END) AS MonthVal,
    MAX(CASE WHEN value LIKE 'Ord[0-9]%' THEN REPLACE(value, 'Ord', '') END) AS OrdVal,
    MAX(CASE WHEN value LIKE 'Cst[0-9]%' THEN REPLACE(value, 'Cst', '') END) AS CstVal
FROM OPENJSON('["' + REPLACE(REPLACE(@test1, ',', ' '), ' ', '","') + '"]')
WHERE value <> ''

上述两种写法对两个测试样例都能准确返回结果：MonthVal=05/2022、OrdVal=195506、CstVal=373175，当Ord/Abo后的数字长度变化、字符串末尾追加其他内容时，提取结果不会出错。

内容的提问来源于stack exchange，提问作者Ivan-Mark Debono