ORACLE REGEXP limitation?

Question

I'm testing oracle REGEXP_SUBSTR function and regexp that works in Python or Web testing tools like https://regex101.com/ doesn't work with Oracle.

Example:

((?:NF\s{0,1}EN){0,1}[\s]{0,1}ISO[\s]{0,1}[\d]{3,6}(?:[\:]{0,1}\d{1,4}){0,1}[\-]{0,1}\d{0,1})

STRING: VAS H M1582/950-80 ABCDFEF - ISO4014

MATCH: ISO4014, but oracle regexp_like doesn't match:

NOT MATCH:

SELECT REGEXP_SUBSTR (
          'VAS H M1582/950-80 ABCDFEF - ISO4014',
          '((?:NF\s{0,1}EN){0,1}[\s]{0,1}ISO[\s]{0,1}[\d]{3,6}(?:[\:]{0,1}\d{1,4}){0,1}[\-]{0,1}\d{0,1})')
  FROM DUAL;

Any idea?

Answer 1

You can use

(NF\s?EN)?\s?ISO\s?\d{3,6}(:?\d{1,4})?-?\d?

See its demo at regex101.com.

Note:

• Oracle regex does not "like" [\s], i.e. shorthand character classes inside brackets, you should not use them like that
• {0,1} is equal to ? (one or zero occurrences)
• (?:...), non-capturing groups, are not supported, you should replace them with capturing groups. (Note that (:? is not a non-capturing group, it is just an optional colon at the start of the second capturing group in the pattern).

Answer 2

You can use my XT_REGEXP for PCRE compatible regular expressions: https://github.com/xtender/XT_REGEXP

select * 
from
 table(xt_regexp.get_matches(
  'VAS H M1582/950-80 ABCDFEF - ISO4014',
  '((?:NF\s{0,1}EN){0,1}[\s]{0,1}ISO[\s]{0,1}[\d]{3,6}(?:[\:]{0,1}\d{1,4}){0,1}[\-]{0,1}\d{0,1})'
));

Results:

COLUMN_VALUE
------------------------------
 ISO4014

1 row selected.