REGEXP_INSTR

Purpose

This function searches the regular expression pattern in the string. If this is not contained, the value 0 is returned, otherwise the corresponding position of the match.

Usage Notes

• For more details and examples on regular expression, refer to the Regular expressions section.
• The optional parameter position defines from which position the search should begin (starting with 1).
• The optional positive number occurrence defines which occurrence should be searched.

The search of the second occurrence begins at the first character after the first occurrence.

• The optional parameter return_opt defines the result of the function in case of a match:

0 (default)	Function returns the beginning position of the match (counting starts from 1)
1	Function returns the end position of the match (character following the occurrence, counting starts from 1)

• REGEXP_INSTR(string,pattern) is similar to REGEXP_INSTR(string,pattern,1,1).
• For additional information, see INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR and the predicate[NOT] REGEXP_LIKE.

Example

SELECT REGEXP_INSTR('Phone: +497003927877678',
                    '\+?\d+'
                   ) REGEXP_INSTR1,
       REGEXP_INSTR('From: my_mail@yahoo.com - To: SERVICE@EXASOL.COM',
                    '(?i)[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,4}',          
                    1,
                    2
                   ) REGEXP_INSTR2;

Result