BigQuery中检测字符串是否包含指定表元素的实现方案问询

方案1：利用正则表达式批量匹配 #

通过将table2中的匹配项拼接成正则规则，结合REGEXP_CONTAINS实现忽略大小写的任意位置包含匹配，同时处理正则特殊字符避免异常。

WITH match_pattern AS (
  SELECT 
    -- 生成带单词边界的忽略大小写正则，转义特殊字符
    CONCAT('(?i)\\b', REGEXP_REPLACE(STRING_AGG(DISTINCT b, '|'), r'([.+*?^$()[\]{}|\\])', r'\\$1'), '\\b') AS pattern
  FROM table2
)
SELECT 
  a,
  CASE WHEN REGEXP_CONTAINS(a, (SELECT pattern FROM match_pattern)) THEN 1 ELSE 0 END AS is_match
FROM table1;

逻辑说明 #

• STRING_AGG(DISTINCT b, '|')：把table2的所有匹配项用|拼接，形成正则“或”匹配规则
• REGEXP_REPLACE(...)：转义b列中的正则特殊字符（如.、*、?），防止匹配逻辑出错
• (?i)：开启忽略大小写模式，无需手动转换字符串大小写
• \\b：匹配单词边界，确保是完整词汇匹配（不需要完整词的话可直接去掉）

方案2：交叉连接+字符串包含判断 #

通过交叉连接关联两张表，用STRING_CONTAINS判断包含关系，最后聚合结果得到是否匹配的标记。

SELECT 
  t1.a,
  MAX(CASE WHEN STRING_CONTAINS(LOWER(t1.a), LOWER(t2.b)) THEN 1 ELSE 0 END) AS is_match
FROM table1 t1
CROSS JOIN table2 t2
GROUP BY t1.a;

逻辑说明 #

• CROSS JOIN：将table1的每一行与table2的所有匹配项逐一关联
• STRING_CONTAINS(LOWER(t1.a), LOWER(t2.b))：统一转小写后判断a是否包含b
• MAX(...)：只要有一个匹配项命中，结果就标记为1，否则为0

示例验证 #

针对你的测试数据：

• table1.a：'abc'、'aBc dEf'、'abc def ghij'
• table2.b：'def'

两种方案都会返回如下结果：

方案对比 #

• 方案1适合table2数据量较小的场景，正则匹配效率更高
• 方案2适合table2数据量较大或需要规避正则特殊字符影响的场景，逻辑更直观

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