如何统计字符串列中最长连续数字长度？修正现有方案缺陷

我们需要统计email表中每个字符串里最长连续数字的个数，以下是示例数据和期望输出：

示例数据表 #

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

期望输出 #

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

原方案的缺陷 #

此前的方案存在两个问题：

• 仅含单个数字的字符串（如lucila1@gmail.com）返回0，正确结果应为1
• 含多段连续数字的字符串（如carlos11perez222@gmail.com）返回两段数字长度之和（5），正确结果应为最长段的长度（3）

MySQL 修正方案 #

通过递归CTE遍历字符串，跟踪连续数字序列并记录最长长度：

WITH RECURSIVE email_digits AS (
    SELECT 
        email,
        email AS remaining_str,
        '' AS current_digit_seq,
        0 AS max_len
    FROM email
    UNION ALL
    SELECT 
        ed.email,
        SUBSTRING(ed.remaining_str, 2),
        CASE 
            WHEN SUBSTRING(ed.remaining_str, 1, 1) REGEXP '[0-9]' THEN CONCAT(ed.current_digit_seq, SUBSTRING(ed.remaining_str, 1, 1))
            ELSE ''
        END AS current_digit_seq,
        CASE 
            WHEN SUBSTRING(ed.remaining_str, 1, 1) REGEXP '[0-9]' THEN GREATEST(ed.max_len, LENGTH(CONCAT(ed.current_digit_seq, SUBSTRING(ed.remaining_str, 1, 1))))
            ELSE ed.max_len
        END AS max_len
    FROM email_digits ed
    WHERE LENGTH(ed.remaining_str) > 0
)
SELECT 
    email,
    COALESCE(MAX(max_len), 0) AS count_cons_digits
FROM email_digits
GROUP BY email
ORDER BY email;

PostgreSQL 修正方案 #

利用正则提取所有连续数字段，直接计算最长段长度：

SELECT 
    email,
    COALESCE(
        (SELECT MAX(LENGTH(digit_seq)) 
         FROM UNNEST(REGEXP_MATCHES(email, '\d+', 'g')) AS digit_seq),
        0
    ) AS count_cons_digits
FROM email
ORDER BY email;

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