Oracle LIKE语句右方括号无法转义的异常问题咨询

测试场景与SQL #

以下是用于验证的测试SQL：

with t as (
select 'name' as s from dual
union all
select 'name[' as s from dual
union all
select 'na[me' as s from dual
union all
select '[name' as s from dual
union all
select 'name]' as s from dual
union all
select 'na]me' as s from dual
union all
select ']name' as s from dual
union all
select 'name[]' as s from dual
union all
select 'na[]me' as s from dual
union all
select '[]name' as s from dual
union all
select '[name]' as s from dual
union all
select 'na[me]' as s from dual
union all
select '[na]me' as s from dual
)
select s from t where t.s like '%]%' escape '\'

测试结果 #

• 执行语句select s from t where t.s like '%]%' escape '\'，可正确返回所有包含]的记录；
• 执行语句select s from t where t.s like '%\]%' escape '\'，触发ORA-01424错误：缺失或非法的转义后字符。

使用的Oracle版本：

Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
"CORE   12.2.0.1.0  Production"
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

异常原因解析 #

1. Oracle LIKE语法的特殊字符规则：
   Oracle的LIKE操作中，只有左括号[是需要转义的特殊字符——它用于开启一个字符范围匹配（例如[a-z]匹配小写字母）。而右括号]仅在与左括号配对、作为范围匹配的结束标记时才有特殊意义，单独出现的]属于普通字符，不需要转义。

2. 两种执行情况的具体分析：

   • 当使用%]%时，Oracle将]视为普通字符串字符，因此能正常匹配所有包含]的记录，即使指定了escape '\'也不会产生冲突，因为没有触发转义逻辑。
   • 当使用%\]%时，你试图用转义字符\去转义]，但Oracle并不认可]是需要转义的字符，这种非法的转义操作直接触发了ORA-01424错误。

3. 文档表述的歧义：
   文档中“需要转义方括号”的描述通常针对左括号[，而非右括号]，这是导致误解的主要原因。

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