如何在Snowflake SQL中提取字符串中的6位发票号（支持多发票识别）

数据集示例 #

1. some text here 123456 some text here
2. some text here #123457 some text here
3. some text here 123458. Some text here
4. Some invoice 123245. Some text with 543903 and 34550
5. Two invoices 124356 and 235478 and some products 6783 and 45639
6. invoice 230943 and invoice 320399. Some text here
7. inv #430203 and #404039. some text here
8. Some invoice 134045 and some text 30+3

当前问题 #

现有方法（如regexp_replace(memo, '[^0-9]', '')或right(regexp_replace(memo, '[^0-9]', ''),6)）仅能处理单发票场景，无法识别多发票情况。

需求说明 #

• 字符串中仅含1或2个发票号，不会超过2个；
• 发票号固定为6位；
• 多发票间必有AND（不区分大小写）分隔，非发票的6位数字与发票号间无AND；
• 理想输出：要么标记"Multiple Invoices"，要么将两张发票号拆分到Inv 1、Inv 2两列。

解决方案（Snowflake SQL） #

可以直接使用Snowflake内置的正则函数实现，无需编写UDF。核心思路是利用正则断言精准匹配6位发票号，并通过AND关键字识别多发票场景：

SELECT
    memo,
    -- 提取第一个6位发票号（支持前缀带#的情况）
    REGEXP_SUBSTR(memo, '(?<!\\d)(?:#?)(\\d{6})(?!\\d)', 1, 1, 'i', 1) AS inv_1,
    -- 提取第二个发票号（仅当存在AND分隔的多发票场景）
    CASE
        WHEN REGEXP_LIKE(memo, '(?<!\\d)#?\\d{6}\\s+and\\s+#?\\d{6}(?!\\d)', 'i')
        THEN REGEXP_SUBSTR(memo, '(?<!\\d)#?(\\d{6})\\s+and\\s+#?(\\d{6})(?!\\d)', 1, 1, 'i', 2)
        ELSE NULL
    END AS inv_2,
    -- 标记多发票状态
    CASE
        WHEN REGEXP_LIKE(memo, '(?<!\\d)#?\\d{6}\\s+and\\s+#?\\d{6}(?!\\d)', 'i')
        THEN 'Multiple Invoices'
        ELSE NULL
    END AS invoice_status
FROM your_table_name;

正则逻辑说明 #

• (?<!\\d)：负向零宽断言，确保匹配的6位数字前无其他数字，避免被更长数字（如1234567）的子串误识别；
• (?:#?)：非捕获组，匹配可选的#前缀（不影响最终提取的数字）；
• (\\d{6})：捕获组，提取目标6位发票号；
• (?!\\d)：负向零宽断言，确保匹配的6位数字后无其他数字，避免误识别长数字的子串；
• 'i'：忽略大小写，兼容And/AND等不同写法。

测试结果匹配示例 #

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