Oracle中REGEXP_LIKE匹配'*'返回false而非true的问题排查

我执行了如下查询语句：

select
    documentat2_.*
    ,case
            when (
                REGEXP_LIKE(documentat2_.VALUE,'^(\\*)$')
            ) then 1
            else 0
        END AS x
from
    DOCUMENT_ATTRIBUTE_BANK documentat1_
inner join
    DOCUMENT_ATTRIBUTE documentat2_
        on documentat1_.DOCUMENT_ATTRIBUTE_ID=documentat2_.DOCUMENT_ATTRIBUTE_ID
where
    exists  (
       select
            documenten0_.DOCUMENT_ID as document_id2_8_
            FROM 
             DOCUMENT documenten0_
        where
            documenten0_.APPLICATION_CODE='xxx' AND 
        documentat1_.DOCUMENT_ID=documenten0_.DOCUMENT_ID
        and documentat1_.DOCUMENT_VERSION=documenten0_.DOCUMENT_VERSION
    )
    and documentat1_.DOCUMENT_VERSION=16
    and documentat1_.BANK_ID='xxxx'
    and documentat2_.CHECKLIST_ID=7
    and documentat2_.NAME='NEW'
    AND documentat2_.VALUE='*'

该查询可正常筛选出VALUE='*'的记录，但REGEXP_LIKE(documentat2_.VALUE,'^(\\*)$')返回0，与预期的1不符。使用REGEXP_LIKE(documentat2_.VALUE,'(\\*)')可正常工作，^(\\*)$却不行；使用REGEXP_LIKE(documentat2_.VALUE, '^(NO)$|^(\\*)$')也能正常工作（因部分记录VALUE为"NO"，部分为"*"）。我认为字段无空格，无法理解此现象，请问问题出在哪里？

这种情况大概率是VALUE字段中存储的*前后存在不可见字符，比如换行符、制表符、全角空格或其他非打印ASCII字符。

你用VALUE='*'能筛选出记录，是因为部分数据库的字符串等值匹配会忽略末尾的空格（比如Oracle的默认行为），但正则表达式的^（匹配开头）和$（匹配结尾）是严格匹配字符串的首尾位置，只要首尾存在额外的隐藏字符，^(\\*)$就无法匹配成功。

验证方法 #

• 执行SELECT LENGTH(documentat2_.VALUE) FROM ...查看该字段的长度，如果长度大于1，说明确实存在额外字符；
• 用SELECT DUMP(documentat2_.VALUE) FROM ...输出字段的ASCII码值，直接查看隐藏的字符编码。

解决办法 #

1. 清理字段隐藏字符：使用正则替换去掉首尾所有空白类字符，比如：

   REGEXP_REPLACE(documentat2_.VALUE, '^[[:space:]]+|[[:space:]]+$', '')
   

   之后再用REGEXP_LIKE匹配；
2. 修改正则表达式：允许首尾存在空白字符，调整为：

   REGEXP_LIKE(documentat2_.VALUE, '^\\s*(\\*)\\s*$')
   

另外你提到^(NO)$|^(\\*)$能正常工作，大概率是因为VALUE='NO'的记录没有隐藏字符，该分支匹配成功让整个正则返回true，但*的记录其实并未匹配到^(\\*)$分支，只是被NO的记录覆盖了整体结果。

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