SQL如何统计字符串列中最长连续数字的长度？

需求描述 #

现有email表存储邮箱地址数据，需要统计每个邮箱地址中出现的最长连续数字序列长度，无数字则返回0。
测试表数据：

email
lucas1234@gmail.com
fer12@gmail.com
lupal@gmail.com
carlos1perez222@gmail.com

预期输出结果：

email  count_cons_digits
      lucas1234@gmail.com                  4
          fer12@gmail.com                  2
          lupal@gmail.com                  0
carlos1perez222@gmail.com                  3

实现方案 #

核心逻辑：将字符串中1个及以上连续的非数字字符替换为统一分隔符，拆分得到所有连续数字片段后取最大长度即可。

MySQL 8.0+ 版本实现 #

通过递归CTE完成字符串拆分，代码可直接运行：

WITH RECURSIVE 
processed AS (
    SELECT 
        email,
        ROW_NUMBER() OVER() AS rid,
        TRIM(BOTH ',' FROM REGEXP_REPLACE(email, '[^0-9]+', ',')) AS digit_str
    FROM email
),
split_seg AS (
    SELECT 
        rid,
        email,
        SUBSTRING_INDEX(digit_str, ',', 1) AS num_segment,
        SUBSTRING(digit_str, LENGTH(SUBSTRING_INDEX(digit_str, ',', 1)) + 2) AS remain_str
    FROM processed
    UNION ALL
    SELECT 
        rid,
        email,
        SUBSTRING_INDEX(remain_str, ',', 1) AS num_segment,
        SUBSTRING(remain_str, LENGTH(SUBSTRING_INDEX(remain_str, ',', 1)) + 2) AS remain_str
    FROM split_seg
    WHERE remain_str != ''
)
SELECT 
    email,
    COALESCE(MAX(LENGTH(num_segment)), 0) AS count_cons_digits
FROM split_seg
GROUP BY rid, email
ORDER BY rid;

PostgreSQL 版本实现 #

借助内置正则拆分函数写法更简洁：

SELECT 
    email,
    COALESCE(MAX(LENGTH(seg)), 0) AS count_cons_digits
FROM email,
LATERAL regexp_split_to_table(email, '[^0-9]+') AS seg
GROUP BY email;

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