使用BigQuery的CONTAINS_SUBSTR判断字段值包含关系的问题及替代方案

BigQuery的CONTAINS_SUBSTR函数第二个参数仅支持字面量或查询参数，无法直接传入字段，你可以用以下两种方法替代：

方法1：使用REGEXP_CONTAINS #

REGEXP_CONTAINS支持将字段作为匹配模式，可直接判断目标字段是否包含指定字段的内容。如果匹配的字段包含正则特殊字符（如.、*、?等），建议用REGEXP_ESCAPE转义，避免匹配逻辑出错。

基础示例（筛选符合条件的记录） #

SELECT ID
FROM (
    SELECT "123" AS ID, "Rama" AS Name, "Rama;HS121;HYD;IND" AS address UNION ALL
    SELECT "124" AS ID, "Sita" AS Name, "Sita;HS131;HYD;IND" AS address UNION ALL
    SELECT "125" AS ID, "Abc" AS Name, "Rama;HS161;HYD;IND" AS address UNION ALL
    SELECT "126" AS ID, "xyz" AS Name, "Sita;HS171;HYD;IND" AS address UNION ALL
    SELECT "127" AS ID, "aaa" AS Name, "HS181;HYD;IND" AS address
)
WHERE REGEXP_CONTAINS(address, Name)

含特殊字符的处理示例 #

SELECT ID, REGEXP_CONTAINS(address, REGEXP_ESCAPE(Name)) AS name_in_address
FROM your_table

方法2：使用STRPOS #

STRPOS函数返回子字符串在父字符串中的起始位置（从1开始计数），若返回值大于0，则表示子字符串存在。这种方法性能更优，适合简单的子字符串匹配场景。

示例（筛选符合条件的记录） #

SELECT ID
FROM (
    SELECT "123" AS ID, "Rama" AS Name, "Rama;HS121;HYD;IND" AS address UNION ALL
    SELECT "124" AS ID, "Sita" AS Name, "Sita;HS131;HYD;IND" AS address UNION ALL
    SELECT "125" AS ID, "Abc" AS Name, "Rama;HS161;HYD;IND" AS address UNION ALL
    SELECT "126" AS ID, "xyz" AS Name, "Sita;HS171;HYD;IND" AS address UNION ALL
    SELECT "127" AS ID, "aaa" AS Name, "HS181;HYD;IND" AS address
)
WHERE STRPOS(address, Name) > 0

方法对比 #

• STRPOS：性能更高，仅支持精确子字符串匹配，无正则功能。
• REGEXP_CONTAINS：支持正则匹配，灵活性更强，适合复杂匹配场景，但性能略低于STRPOS。

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