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?
Wrap the expression that you want to match in a capturing group and then unwrap the look-behind and then just extract the value of the capturing group with the 6th argument of REGEXP_SUBSTR:
SELECT REGEXP_SUBSTR('1331@iwantthis@3ad44@2', '\d{4}\@(.\w+)', 1, 1, NULL, 1 )
FROM DUAL;
Which outputs:
| REGEXP_SUBSTR('1331@IWANTTHIS@3AD44@2','\D{4}\@(.\W+)',1,1,NULL,1) |
| :----------------------------------------------------------------- |
| iwantthis |
db<>fiddle here
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.