CODEWITHSUNDEEP
regexoracle-database
Jeremy
Jeremy

Reputation: 5435

Oracle SQL Regex not returning expected results

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

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

Examples: https://regex101.com/r/oH6jV0/1

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,}');

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

Upvotes: 1

Views: 76

Answers (1)

MT0
MT0

Reputation: 167962

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

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 Fiddle

Oracle 11g R2 Schema Setup:

Query 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 |

Upvotes: 4

Related Questions