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 optional parameter
return_opt
defines the result of the function in case of a match: REGEXP_INSTR(string,pattern)
is similar toREGEXP_INSTR(string,pattern,1,1)
.- For additional information, see INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR and the predicate[NOT] REGEXP_LIKE.
The search of the second occurrence begins at the first character after the first occurrence.
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) |
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 |