SSMS中替换字符串内连续数字为序列123及分组统计的技术需求

问题描述 #

公司发票命名规则分为两类：

• 系统销售订单：CINVBxxx563901（数字段位置、长度不固定）
• 自由文本发票：FTINVBxxx00120674-DM（数字段可位于中间，长度不固定）

需求：将字符串中所有连续数字段替换为等长度的连续123序列，示例：

• CINVBxxx563901 → CINVBxxx123456
• FTINVBxxx00120674-DM → FTINVBxxx12345678-DM

现有代码仅支持数字段在末尾的场景：

TRIM('1234567890' FROM [INVOICE])+LEFT('12345678901234567890',LEN([INVOICE]),LEN(TRIM('1234567890' FROM [INVOICE]))

需基于处理后的发票规则+业务线做GROUP BY COUNT(*)，当前仅能通过Excel嵌套REPLACE/SUBSTITUTE实现，但数据集达8200万条，急需高效方案。

高效解决方案（按数据库分类） #

针对大数据量场景，优先使用数据库原生函数或自定义函数实现，避免Excel处理。

1. SQL Server #

方法：自定义标量函数（兼容全版本）

CREATE FUNCTION dbo.ReplaceDigitsWith123(@input NVARCHAR(255))
RETURNS NVARCHAR(255)
AS
BEGIN
    DECLARE @pos INT = PATINDEX('%[0-9]%', @input)
    WHILE @pos > 0
    BEGIN
        -- 计算当前数字段长度
        DECLARE @digitLen INT = 0
        WHILE SUBSTRING(@input, @pos + @digitLen, 1) BETWEEN '0' AND '9'
        BEGIN
            SET @digitLen += 1
        END
        -- 生成对应长度的123序列
        DECLARE @replaceStr NVARCHAR(255) = ''
        DECLARE @i INT = 1
        WHILE @i <= @digitLen
        BEGIN
            SET @replaceStr += CAST(((@i - 1) % 10) + 1 AS CHAR(1))
            SET @i += 1
        END
        -- 替换数字段
        SET @input = STUFF(@input, @pos, @digitLen, @replaceStr)
        -- 定位下一个数字段
        SET @pos = PATINDEX('%[0-9]%', @input)
    END
    RETURN @input
END
GO

-- 分组统计查询
SELECT 
    dbo.ReplaceDigitsWith123([INVOICE]) AS invoice_rule,
    business_line,
    COUNT(*) AS record_count
FROM your_invoice_table
GROUP BY dbo.ReplaceDigitsWith123([INVOICE]), business_line

性能优化：内联表值函数（推荐大数据量）

标量函数在8200万条数据上可能存在性能瓶颈，可改用内联表值函数实现基于集合的替换逻辑，减少逐行循环开销。

2. PostgreSQL #

方法：正则替换+生成序列

利用PostgreSQL的正则匹配和generate_series生成对应长度的123序列：

CREATE OR REPLACE FUNCTION replace_digits_with_123(input_str TEXT)
RETURNS TEXT AS $$
DECLARE
    result_str TEXT := input_str;
    digit_match TEXT;
BEGIN
    LOOP
        -- 匹配第一个数字段
        SELECT regexp_match(result_str, '\d+') INTO digit_match;
        EXIT WHEN digit_match IS NULL;
        
        -- 生成对应长度的123序列
        DECLARE
            digit_len INT := length(digit_match);
            replace_str TEXT := '';
        BEGIN
            FOR i IN 1..digit_len LOOP
                replace_str := replace_str || ((i-1)%10 + 1)::TEXT;
            END LOOP;
            -- 替换当前数字段
            result_str := regexp_replace(result_str, '\d+', replace_str, 1, 1);
        END;
    END LOOP;
    RETURN result_str;
END;
$$ LANGUAGE plpgsql;

-- 分组统计查询
SELECT
    replace_digits_with_123(invoice) AS invoice_rule,
    business_line,
    COUNT(*) AS record_count
FROM your_invoice_table
GROUP BY replace_digits_with_123(invoice), business_line;

3. MySQL #

方法：自定义函数+正则定位

DELIMITER //
CREATE FUNCTION replace_digits_with_123(input_str VARCHAR(255))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    DECLARE pos INT;
    DECLARE digit_len INT;
    DECLARE replace_str VARCHAR(255);
    DECLARE i INT;
    
    -- 定位第一个数字段
    SET pos = REGEXP_INSTR(input_str, '[0-9]+');
    WHILE pos > 0 DO
        -- 获取数字段长度
        SET digit_len = LENGTH(REGEXP_SUBSTR(input_str, '[0-9]+', pos));
        -- 生成123序列
        SET replace_str = '';
        SET i = 1;
        WHILE i <= digit_len DO
            SET replace_str = CONCAT(replace_str, MOD(i-1, 10) + 1);
            SET i = i + 1;
        END WHILE;
        -- 替换数字段
        SET input_str = INSERT(input_str, pos, digit_len, replace_str);
        -- 定位下一个数字段
        SET pos = REGEXP_INSTR(input_str, '[0-9]+', pos + LENGTH(replace_str));
    END WHILE;
    RETURN input_str;
END //
DELIMITER ;

-- 分组统计查询
SELECT
    replace_digits_with_123(invoice) AS invoice_rule,
    business_line,
    COUNT(*) AS record_count
FROM your_invoice_table
GROUP BY replace_digits_with_123(invoice), business_line;

通用性能优化建议 #

1. 预处理存储：将处理后的发票规则存入新字段（如invoice_rule），并建立联合索引(invoice_rule, business_line)，后续直接基于索引分组统计，这是8200万条数据最高效的方案。
2. 避免标量函数：若数据库支持，优先使用内联表值函数或基于集合的正则替换逻辑，减少逐行循环的性能开销。
3. 分区扫描：若表已按business_line分区，分组时可利用分区过滤减少数据扫描范围。

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