PostgreSQL正则匹配中文字符正常，BigQuery结果异常求解决

问题场景 #

在PostgreSQL中，通过正则表达式[\x4e00-\x9fff\x3400-\x4dbf]可以准确筛选出包含中文字符的字符串，测试代码及结果如下：

PostgreSQL测试代码 #

with tmp as (
    select '中文zz' as word
    union all
    select '中文' as word
    union all
    select 'english' as word
    union all
    select 'にほんご' as word
    union all
    select 'eng–lish' as word
)
select word,
word ~* '[\x4e00-\x9fff\x3400-\x4dbf]'
from tmp 

PostgreSQL执行结果 #

中文zz       true
中文         true
english     false
にほんご     false
eng–lish    false

但将逻辑迁移到BigQuery后，使用regexp_contains(word, r'[\x4e00-\x9fff\x3400-\x4dbf]')得到的结果完全不符合预期：

BigQuery测试代码 #

with tmp as (
    select '中文zz' as word
    union all
    select '中文' as word
    union all
    select 'english' as word
    union all
    select 'にほんご' as word
    union all
    select 'eng–lish' as word
)
select word,
regexp_contains(word, r'[\x4e00-\x9fff\x3400-\x4dbf]')
from tmp

BigQuery异常结果 #

中文zz      true
中文        false
english     true
にほんご     false
eng–lish    true

解决方法 #

BigQuery采用RE2正则引擎，对Unicode字符的处理规则和PostgreSQL不同，以下两种方案可实现正确匹配：

方案1：使用Unicode属性类（推荐） #

直接使用\p{Han}匹配所有中日韩统一表意文字，写法简洁且覆盖完整：

with tmp as (
    select '中文zz' as word
    union all
    select '中文' as word
    union all
    select 'english' as word
    union all
    select 'にほんご' as word
    union all
    select 'eng–lish' as word
)
select word,
regexp_contains(word, r'[\p{Han}]')
from tmp

方案2：修正十六进制字符范围 #

将PostgreSQL中的\x替换为RE2支持的\u来表示Unicode字符：

with tmp as (
    select '中文zz' as word
    union all
    select '中文' as word
    union all
    select 'english' as word
    union all
    select 'にほんご' as word
    union all
    select 'eng–lish' as word
)
select word,
regexp_contains(word, r'[\u4E00-\u9FFF\u3400-\u4DBF]')
from tmp

预期执行结果 #

两种方案都会得到和PostgreSQL一致的正确结果：

中文zz       true
中文         true
english     false
にほんご     false
eng–lish    false

原因解析 #

• PostgreSQL的正则引擎允许用\x表示Unicode字符，但RE2中\x仅对应ASCII字符的十六进制转义，这是导致原正则失效的核心原因。
• \p{Han}是RE2支持的Unicode属性类，专门匹配中文字符及相关表意文字，是更可靠的写法，无需手动维护字符范围。

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