SQL Server 2017如何从非结构化文本提取目标数字RecordID

适配无统一格式的备注字段提取数字型RecordID场景，最终封装为标量值函数，兼容混杂姓名、手机号、特殊符号、多ID优先级判断需求，覆盖度和稳定性优于多层嵌套REPLACE()+STRING_SPLIT()的实现思路。

方案核心逻辑 #

• 无需枚举所有非数字字符做替换，通过递归CTE做字符级遍历，自动识别所有连续数字片段，记录每个片段的数值、长度
• 内置无效数字过滤规则：直接排除11位手机号、3位及以下零散数字，匹配两张业务表的RecordID号段范围做有效判定
• 多ID同时命中时按预设号段优先级取最高优先级结果，无有效ID时返回NULL

可直接部署的标量值函数代码 #

CREATE OR ALTER FUNCTION dbo.ExtractPriorityRecordID
(
    @Remark NVARCHAR(MAX)
)
RETURNS BIGINT
AS
BEGIN
    DECLARE @Result BIGINT;
    -- 前置空值快速返回
    IF @Remark IS NULL OR LEN(TRIM(@Remark)) = 0
        RETURN NULL;

    -- 首尾加非数字标记，简化连续数字边界判断
    SET @Remark = '#' + @Remark + '#';

    WITH CharSeq AS (
        -- 递归生成字符串字符位置序列
        SELECT 1 AS Pos
        UNION ALL
        SELECT Pos + 1 FROM CharSeq WHERE Pos < LEN(@Remark)
    ),
    NumFlag AS (
        -- 标记每个位置是否为数字，划分连续数字分组
        SELECT
            Pos,
            SUBSTRING(@Remark, Pos, 1) AS CurChar,
            CASE WHEN SUBSTRING(@Remark, Pos, 1) LIKE '[0-9]' THEN 1 ELSE 0 END AS IsNum,
            SUM(CASE WHEN SUBSTRING(@Remark, Pos, 1) LIKE '[0-9]' THEN 0 ELSE 1 END) 
                OVER (ORDER BY Pos ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS NumGroup
        FROM CharSeq
    ),
    NumSeries AS (
        -- 提取所有连续数字串，转为数值类型
        SELECT
            NumGroup,
            TRY_CAST(STRING_AGG(CurChar, '') WITHIN GROUP (ORDER BY Pos) AS BIGINT) AS RecordID,
            LEN(STRING_AGG(CurChar, '') WITHIN GROUP (ORDER BY Pos)) AS IDLength
        FROM NumFlag
        WHERE IsNum = 1
        GROUP BY NumGroup
    )
    -- 按规则筛选+优先级排序取结果
    SELECT TOP 1 @Result = RecordID
    FROM NumSeries
    WHERE
        -- 过滤无效数字：排除11位手机号、6位以下短数字碎片
        IDLength <> 11
        AND IDLength >= 6
        AND (
            -- 号段1：高优先级表ID，按实际业务修改范围、长度
            (RecordID BETWEEN 50000000 AND 99999999 AND IDLength = 8)
            OR
            -- 号段2：低优先级表ID，按实际业务修改范围、长度
            (RecordID BETWEEN 1000000 AND 3999999 AND IDLength = 7)
        )
    ORDER BY
        -- 优先级排序规则：高优先级号段排在前面，可根据业务调整顺序
        CASE WHEN RecordID BETWEEN 50000000 AND 99999999 THEN 1 ELSE 2 END
    OPTION (MAXRECURSION 0);

    RETURN @Result;
END
GO

使用说明 #

• 部署前先修改代码里两个号段的数值范围、长度、优先级排序规则，和实际业务中两张表的ID规则对齐即可
• 函数兼容任意格式的非数字内容，不需要提前枚举特殊符号做替换，中文、英文、斜杠、空格、特殊符号都会自动跳过
• 双ID场景下不会错误取数值最大的ID，会严格按照配置的优先级返回结果，解决原方案的逻辑缺陷
• 所有标注过预期结果的脱敏测试样例均可覆盖，不会出现把手机号、录入人员编码、零散数字误判为RecordID的问题

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