BigQuery正则表达式过大报错：95个名称匹配的高效替代方案

问题场景 #

需要对目标列的字符串进行三类匹配标记：

• 包含指定名称列表中的名称，且名称前后带有连字符（如John-Brown或Brown-John）→ 标记为 -1
• 包含名称但无连字符 → 标记为 1
• 不包含任何指定名称 → 标记为 0

原SQL通过拼接名称为正则表达式实现，但当名称数量达95个、处理数百万行数据时，触发报错：

Cannot parse regular expression: pattern too large - compile failed.

替代实现方案 #

方案一：展开名称列表逐行匹配后聚合 #

通过UNNEST展开名称列表，对每个名称单独判断匹配类型，最后通过聚合函数取优先级最高的标记（-1 > 1 > 0）。

WITH name_list AS (
    SELECT name FROM UNNEST(['John', 'David', 'Alice', 'Michael' /* 补充剩余91个名称 */]) AS name
),
matched_flags AS (
    SELECT
        t.id,
        t.column_to_check,
        MAX(CASE
            -- 覆盖三种带连字符的场景：前后都有、开头后跟连字符、结尾前有连字符
            WHEN (INSTR(t.column_to_check, CONCAT('-', nl.name, '-')) > 0) 
                OR (t.column_to_check LIKE CONCAT(nl.name, '-%')) 
                OR (t.column_to_check LIKE CONCAT('%', '-', nl.name))
            THEN -1
            -- 无连字符的匹配场景
            WHEN INSTR(t.column_to_check, nl.name) > 0 THEN 1
            ELSE 0
        END) AS flag
    FROM table_name t
    CROSS JOIN name_list nl
    GROUP BY t.id, t.column_to_check
)
SELECT id, column_to_check, flag FROM matched_flags;

方案二：使用EXISTS子查询提前终止匹配 #

通过EXISTS子查询分别检查两种匹配场景，一旦找到符合条件的名称就停止后续检查，性能更优。

WITH name_list AS (
    SELECT name FROM UNNEST(['John', 'David', 'Alice', 'Michael' /* 补充剩余91个名称 */]) AS name
)
SELECT
    t.id,
    t.column_to_check,
    CASE
        -- 先检查是否存在带连字符的匹配
        WHEN EXISTS (
            SELECT 1 FROM name_list nl
            WHERE (INSTR(t.column_to_check, CONCAT('-', nl.name, '-')) > 0)
                OR (t.column_to_check LIKE CONCAT(nl.name, '-%'))
                OR (t.column_to_check LIKE CONCAT('%', '-', nl.name))
        ) THEN -1
        -- 再检查是否存在无连字符的匹配
        WHEN EXISTS (
            SELECT 1 FROM name_list nl
            WHERE INSTR(t.column_to_check, nl.name) > 0
        ) THEN 1
        ELSE 0
    END AS flag
FROM table_name t;

方案说明 #

两种方案均避免了构建超长正则表达式，改用逐名称匹配的方式：

• 方案一适合需要保留中间匹配细节的场景，聚合逻辑简单直接
• 方案二利用EXISTS的短路特性，在大数据量下性能更出色
• 两种方法都完整覆盖了所有带连字符的匹配场景，确保标记逻辑准确

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