Oracle SQL Regex not returning expected results

Question

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.

MT0 · Accepted Answer · 2015-09-09 22:39:51Z

3

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 |

edited Sep 9, 2015 at 22:39

answered Sep 9, 2015 at 22:31

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeremy Over a year ago

Cool. This seems mostly right (for what it is), but it's not matching spaces e.g. '12 135'.

MT0 Over a year ago

@Jeremy Updated - swap perl \s for POSIX [:space:] inside the character class (as others have already mentioned).

Collectives™ on Stack Overflow

Oracle SQL Regex not returning expected results

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related