REGEXP_INSTR function returns a number for the position of matched pattern.
Unlike INSTR, REGEXP_INSTR cannot work from the end of the string backward.
The arguments for REGEXP_INSTR are:
REGEXP_INSTR(String to search, Pattern, [Position,[Occurrence, [Return-option, [Parameters]]]])
- Pattern is the sought string, which will be expressed as an RE.
- These first two arguments are not optional.
- Position is the place in 'String to search' to begin the search for Pattern. The default is 1.
SQL> SELECT REGEXP_INSTR('This is a test','a') position FROM dual;