Search code examples
regexoracle-databasepcre

Oracle - PCRE to Oracle REGEX

I want to extract everything after the first occurrence and the second occurrence of '@':

1331@iwantthis@3ad44@2

In PCRE I would do this like that (at least for the given example):

(?<=\d{4}\@).\w+

Since Oracle DBMS does not support PCRE, how can I extract the desired substring?

Solution

  • In Oracle, you may use

    SELECT REGEXP_SUBSTR('1331@iwantthis@3ad44@2', '[^@]+', 1, 2)

    See the online demo.

    This will extract the second occurrence (as the occurrence argument is set to 2) starting from the first symbol in the string (as the position argument is set to 1) of a [^@]+ pattern that matches one or more chars other than @ char as many as possible.

    See more details on the REGEXP_SUBSTR reference page.