如何在REGEXP_CONTAINS中使用另一张表的子串列表做匹配

你遇到的报错根本原因是：REGEXP_CONTAINS 函数的第二个入参仅支持接收单个正则表达式字符串，你原来的写法中子查询返回了子串表的多行结果，不符合参数要求自然会触发报错。

关于while循环的可行性 #

while循环理论上可以实现需求，但属于性价比极低的方案：你需要额外声明变量、遍历子串表、把匹配结果存入临时表汇总，不仅逻辑冗余，执行效率也远低于原生SQL的集合运算，完全不推荐使用。

推荐实现方案 #

方案1：拼接子串为单个正则表达式 #

用 STRING_AGG 函数把所有待匹配子串用正则或运算符|拼接为单个字符串，直接传入REGEXP_CONTAINS即可，写法最简洁：

SELECT REGEXP_REPLACE(table_name, '^RESULT_', '') AS table_names
FROM projects.dataset.INFORMATION_SCHEMA.TABLES
WHERE REGEXP_CONTAINS(table_name, '[0-9]')
AND REGEXP_CONTAINS(table_name, (
    -- 把所有子串拼接为 子串1|子串2|子串3 格式的正则
    SELECT STRING_AGG(SubStrings, '|')
    FROM project.dataset.SubStringTable
))
ORDER BY table_name

注意：如果你的子串包含.、*、+这类正则特殊字符，需要先转义避免匹配异常，把拼接逻辑改成 STRING_AGG(REGEXP_ESCAPE(SubStrings), '|') 即可。

方案2：用EXISTS子查询匹配 #

如果子串数量非常多，拼接后的正则长度超过限制，可以采用逐行匹配的写法，执行逻辑更稳定：

SELECT REGEXP_REPLACE(t.table_name, '^RESULT_', '') AS table_names
FROM projects.dataset.INFORMATION_SCHEMA.TABLES t
WHERE REGEXP_CONTAINS(t.table_name, '[0-9]')
AND EXISTS (
    -- 只要表名匹配任意一个子串就返回结果
    SELECT 1
    FROM project.dataset.SubStringTable s
    WHERE REGEXP_CONTAINS(t.table_name, s.SubStrings)
)
ORDER BY t.table_name

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