oracle regex

Oracle regex match

To select a matched string with a prefix but not include the prefix

In the following example, it searches for number that follows an abc prefix, but return only the number

select regexp_substr('jdfal2sk12jdfabc123fadkljd1234fl','(abc)([0-9]+)',1,1,null,2) from dual   

This will return 123

regexp_substr

The first parameter is the full text

the second parameter is the regex pattern

the third parameter is the starting position

the fourth is the occurrence

the fifth !!! specifiies which matched group of the regex pattern is returned.

In the example, the (abc) is the first group, enclosed by brackets, the second group ([0-9]+) is the number.

In python or .net or anywhere that supports full version of regex

(?<=abc)[0-9]+ will do the trick

but the lookbehind (?<=abc) is not supported by oracle