Snowflake SQL实现字符串提取7-8位数字并去重的问题求助

问题根源 #

你现有代码的问题出在三个地方：

• REGEXP_SUBSTR的第4个参数指定匹配项的位置，你全部写死为1，每次都会提取第一个匹配到的8位数字，所以返回三个重复结果
• 正则规则仅匹配8位数字，没有覆盖7位数字的需求
• 硬编码拼接3个匹配结果，无法处理超过3个匹配项的场景，也没有去重逻辑

解决方案 #

你使用的是支持POSIX正则的Oracle类SQL语法，可通过递归展开所有匹配项后去重聚合实现需求，适配任意数量的匹配结果：

WITH source_data AS (
    -- 替换为你的原始查询逻辑
    SELECT DESCRIPTION AS string 
    FROM 你的业务表
),
all_matched AS (
    SELECT 
        string,
        REGEXP_SUBSTR(REPLACE(string, ' '), '[[:digit:]]{7,8}', 1, LEVEL) AS single_num
    FROM source_data
    CONNECT BY 
        -- 递归层级等于匹配到的数字总个数
        LEVEL <= REGEXP_COUNT(REPLACE(string, ' '), '[[:digit:]]{7,8}')
        AND PRIOR string = string
        AND PRIOR SYS_GUID() IS NOT NULL -- 避免递归循环报错
)
SELECT 
    string,
    -- 去重后用空格拼接所有唯一数字
    LISTAGG(DISTINCT single_num, ' ') WITHIN GROUP (ORDER BY single_num) AS Num_Value
FROM all_matched
WHERE single_num IS NOT NULL
GROUP BY string
HAVING Num_Value IS NOT NULL;

如果你使用的是PostgreSQL，可使用更简洁的regexp_matches语法实现：

SELECT 
    string,
    STRING_AGG(DISTINCT num[1], ' ' ORDER BY num[1]) AS Num_Value
FROM (
    SELECT 
        DESCRIPTION AS string,
        regexp_matches(REPLACE(DESCRIPTION, ' '), '[0-9]{7,8}', 'g') AS num
    FROM 你的业务表
) t
GROUP BY string
HAVING Num_Value IS NOT NULL;

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