SQL Server 2016提取连字符前后连续数字的技术实现问询

需求说明 #

在SQL Server 2016环境下，需要从自由格式的文本列中提取满足以下条件的子串并生成新列：

• 子串由连字符'-'前后直接相连的数字组成（即'-'与前后数字之间无空格或其他非数字字符）
• 排除连字符与数字之间有空格分隔的情况
• 若一行文本中有多个匹配项，取第一个匹配的子串（如示例中第三行仅提取123456-123456）

示例数据 #

假设表名为YourTable，文本列名为FreeText，原始数据如下：

CREATE TABLE YourTable (FreeText NVARCHAR(100));
INSERT INTO YourTable VALUES
('Widget 1234-12345 Blah Blah'),
('12345-12345_A'),
('123456-123456 and 6789-12345'),
('12 1234567-1234567');

解决方案代码 #

使用PATINDEX定位匹配模式，结合SUBSTRING提取目标子串：

SELECT 
    FreeText,
    SUBSTRING(
        FreeText,
        PATINDEX('%[0-9][0-9]*-[0-9][0-9]*%', FreeText),
        -- 计算匹配子串的总长度：前缀数字长度 + 1（连字符） + 后缀数字长度
        CHARINDEX('-', FreeText, PATINDEX('%[0-9][0-9]*-[0-9][0-9]*%', FreeText)) 
        - PATINDEX('%[0-9][0-9]*-[0-9][0-9]*%', FreeText) 
        + 1 
        + PATINDEX('%[^0-9]%', SUBSTRING(FreeText, CHARINDEX('-', FreeText, PATINDEX('%[0-9][0-9]*-[0-9][0-9]*%', FreeText)) + 1, LEN(FreeText)) + ' ') - 1
    ) AS ExtractedNumberPair
FROM YourTable;

代码解释 #

1. PATINDEX('%[0-9][0-9]*-[0-9][0-9]*%', FreeText)：定位第一个符合数字串-数字串格式的子串起始位置
2. CHARINDEX('-', FreeText, ...)：找到该子串中'-'的位置，以此分割前缀数字和后缀数字
3. 长度计算逻辑：
   • 前缀数字长度 = '-'的位置 - 子串起始位置
   • 后缀数字长度 = 通过嵌套PATINDEX找到'-'后第一个非数字字符的位置，从而确定连续数字的长度
4. 最终通过SUBSTRING截取完整的数字-数字格式子串

输出结果 #

运行上述代码后，ExtractedNumberPair列的结果如下：

1234-12345
12345-12345
123456-123456
1234567-1234567

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