在SQL Server中识别重复数字并提取重复部分的实现方法

方案思路 #

针对不符合10-550范围的数值，我们通过字符串匹配识别重复子串，提取后验证是否符合目标范围，避免简单截取带来的无效数据问题。以下是具体实现：

1. 创建自定义标量函数 #

这个函数会检查数值的字符串形式是否存在重复子串，提取后返回符合范围的结果：

CREATE FUNCTION dbo.ExtractRepeatedPattern(@num INT)
RETURNS INT
AS
BEGIN
    DECLARE @str VARCHAR(20) = CAST(@num AS VARCHAR(20));
    DECLARE @len INT = LEN(@str);
    DECLARE @result INT = NULL;

    -- 处理偶数长度的半串重复（如7676→76、123123→123）
    IF @len % 2 = 0 
        AND SUBSTRING(@str, 1, @len/2) = SUBSTRING(@str, @len/2 + 1, @len/2)
    BEGIN
        SET @result = CAST(SUBSTRING(@str, 1, @len/2) AS INT);
    END
    -- 处理3次重复的子串（如919191→91）
    ELSE IF @len % 3 = 0 
        AND SUBSTRING(@str, 1, @len/3) = SUBSTRING(@str, @len/3 + 1, @len/3)
        AND SUBSTRING(@str, 1, @len/3) = SUBSTRING(@str, 2*@len/3 + 1, @len/3)
    BEGIN
        SET @result = CAST(SUBSTRING(@str, 1, @len/3) AS INT);
    END
    -- 处理全相同字符的情况（如5555→5，但因不在范围返回NULL）
    ELSE IF @len >= 2 AND REPLICATE(SUBSTRING(@str,1,1), @len) = @str
    BEGIN
        SET @result = CAST(SUBSTRING(@str,1,1) AS INT);
    END

    -- 验证结果是否在10-550范围内，不符合则返回NULL
    IF @result BETWEEN 10 AND 550
        RETURN @result;
    ELSE
        RETURN NULL;
END
GO

2. 使用函数提取有效数据 #

查询不符合范围的记录，同时提取有效重复模式的分数：

SELECT 
    OriginalScore,
    dbo.ExtractRepeatedPattern(OriginalScore) AS ExtractedValidScore
FROM YourTestScoresTable
WHERE OriginalScore NOT BETWEEN 10 AND 550;

方案说明 #

• 仅识别明确的重复模式：对于无重复的无效数据（如123456），函数会返回NULL，符合你的需求；
• 接受123123这类重复模式的处理风险：这类数据会被提取为123，若在10-550范围内则保留；
• 可扩展性：如果需要支持更多重复模式（如4次重复的子串），可以在函数中添加对应的判断逻辑。

内容的提问来源于stack exchange，提问作者Marja van der Wind