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.

Syntax

regexp_instr::=

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
REGEXP_INSTR1 REGEXP_INSTR2
8 31