CODEWITHSUNDEEP
sqlregexoracle-databaseregexp-substrregexp-like
Antoine Auzillaud
Antoine Auzillaud

Reputation: 21

ORACLE REGEXP limitation?

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?

Upvotes: 2

Views: 770

Answers (2)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8665

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.

Upvotes: 0

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627344

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).

Upvotes: 1

Related Questions