从多列自由文本提取合规ID字符串（SQL实现，无自定义函数）

核心思路 #

分两类场景处理：优先取ID列的有效ID，再从Comments中提取带标识的有效ID，同时排除全0、非数字、4位年份等干扰项，全程不用自定义函数。

有效ID判定规则 #

• 纯数字字符串，长度≥4
• 不是全0（如0000、00000这类都排除）
• 排除常见4位年份（1900-2099区间的数字，可根据实际业务调整范围）

最终SQL代码（以SQL Server为例） #

-- 场景1：ID列本身有有效ID
SELECT DISTINCT
    NAME,
    ID AS 合规ID,
    'ID column' AS MentionedAs
FROM [TABLE]
WHERE
    -- 验证ID是纯数字
    ID NOT LIKE '%[^0-9]%'
    -- 长度至少4位
    AND LEN(ID) >= 4
    -- 排除全0
    AND REPLACE(ID, '0', '') <> ''

UNION ALL

-- 场景2：ID列无效，从Comments中提取有效ID
SELECT DISTINCT
    t.NAME,
    -- 提取Comments中符合规则的数字串
    SUBSTRING(t.COMMENTS, p.Pos, p.Length) AS 合规ID,
    -- 判定来源类型
    CASE
        WHEN t.COMMENTS LIKE '%ID%' + SUBSTRING(t.COMMENTS, p.Pos, p.Length) + '%'
             OR SUBSTRING(t.COMMENTS, p.Pos, p.Length) + '%' LIKE '%ID%'
             THEN 'ID提及'
        WHEN t.COMMENTS LIKE '%code%' + SUBSTRING(t.COMMENTS, p.Pos, p.Length) + '%'
             OR SUBSTRING(t.COMMENTS, p.Pos, p.Length) + '%' LIKE '%code%'
             THEN 'code提及'
    END AS MentionedAs
FROM [TABLE] t
-- 用PATINDEX定位Comments中符合规则的数字起始位置
CROSS APPLY (
    SELECT
        PATINDEX('%[0-9][0-9][0-9][0-9]%', t.COMMENTS) AS Pos,
        -- 找到连续数字的长度
        PATINDEX('%[^0-9]%', SUBSTRING(t.COMMENTS, PATINDEX('%[0-9][0-9][0-9][0-9]%', t.COMMENTS), LEN(t.COMMENTS))) - 1 AS Length
    WHERE
        -- ID列无效（全0、非数字、长度不足4）
        (t.ID IS NULL OR t.ID = '' OR t.ID LIKE '%[^0-9]%' OR LEN(t.ID) < 4 OR REPLACE(t.ID, '0', '') = '')
        -- Comments中存在至少4位连续数字
        AND PATINDEX('%[0-9][0-9][0-9][0-9]%', t.COMMENTS) > 0
) p
WHERE
    -- 提取的数字串不是全0
    REPLACE(SUBSTRING(t.COMMENTS, p.Pos, p.Length), '0', '') <> ''
    -- 排除4位年份（1900-2099）
    AND NOT (
        SUBSTRING(t.COMMENTS, p.Pos, 4) BETWEEN '1900' AND '2099'
        AND LEN(SUBSTRING(t.COMMENTS, p.Pos, p.Length)) = 4
    )

代码说明 #

1. ID列处理：直接筛选纯数字、长度≥4、非全0的ID，标记来源为ID column
2. Comments提取逻辑：
   • 用PATINDEX定位连续4位及以上数字的起始位置，再计算连续数字的长度
   • 通过CROSS APPLY把提取逻辑封装，避免重复代码
   • 匹配数字串前后是否有ID或code关键字，判定来源类型
   • 额外排除1900-2099的4位年份，避免干扰
3. 去重与合并：用UNION ALL合并两类场景，DISTINCT确保结果无重复

适配其他SQL方言的调整 #

• MySQL：把PATINDEX换成REGEXP_INSTR，SUBSTRING换成SUBSTR，年份判断用SUBSTR(...,1,4) BETWEEN 1900 AND 2099
• Oracle：用REGEXP_INSTR定位，REGEXP_SUBSTR直接提取连续数字串，语法调整为REGEXP_SUBSTR(COMMENTS, '[0-9]{4,}')

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