Oracle SQL Regex未返回预期结果 [英] Oracle SQL Regex not returning expected results

问题描述

我使用的正则表达式可以在Java/PHP/regex测试器中很好地工作.

I am using a regex that works perfectly in Java/PHP/regex testers.

\d(?:[()\s#-]*\d){3,}

示例: https://regex101.com/r/oH6jV0/1​​

但是，尝试在Oracle SQL中使用相同的正则表达式不会返回任何结果.例如:

However, trying to use the same regex in Oracle SQL is returning no results. Take for example:

select *
from
(select column_value str from table(sys.dbms_debug_vc2coll('123','1234','12345','12 135', '1', '12 3')))
where regexp_like(str, '\d(?:[()\s#-]*\d){3,}');

这将不返回任何行.为什么这样做的行为如此不同?我什至使用了执行POSIX ERE的正则表达式测试器，但仍然可以使用.

This returns no rows. Why does this act so differently? I even used a regex tester that does POSIX ERE, but that still works.

推荐答案

Oracle不支持非捕获组(?:).您将需要改用捕获组.

Oracle does not support non-capturing groups (?:). You will need to use a capturing group instead.

它也不喜欢字符类[]中的perl样式的空白元字符\s匹配(它将匹配字符\和s而不是空白).您将需要使用POSIX表达式[:space:]代替.

It also doesn't like the perl-style whitespace meta-character \s match inside a character class [] (it will match the characters \ and s instead of whitespace). You will need to use the POSIX expression [:space:] instead.

SQL小提琴

Oracle 11g R2架构设置:

查询1 :

select *
from (
  select column_value str
  from   table(sys.dbms_debug_vc2coll('123','1234','12345','12 135', '1', '12 3'))
)
where regexp_like(str, '\d([()[:space:]#-]*\d){3,}')

结果 :

Results:

|    STR |
|--------|
|   1234 |
|  12345 |
| 12 135 |

这篇关于Oracle SQL Regex未返回预期结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！