REGEXP_SUBSTR
Purpose
This function returns a substring of the parameter string
.
Syntax
regexp_substring::=
Usage Notes
- For details and examples for regular expressions, refer to Regular Expressions section.
- This function is similar to the function REGEXP_INSTR, but it returns the whole matching substring instead of returning the position of the match.
- The parameter
pattern
defines a regular expression to be searched for. If no match is found,NULL
is returned. Otherwise the corresponding substring is returned. - The optional parameter
position
defines from which position the search shall begin (starting with 1). - The optional positive number
occurrence
defines which occurrence shall be searched for. Please note that the search of the second occurrence begins at the first character after the first occurrence. REGEXP_SUBSTR(string,pattern)
is similar toREGEXP_SUBSTR(string,pattern,1,1)
.- For additional information, see functions SUBSTR[ING], REGEXP_INSTR, and REGEXP_REPLACE and the predicate [NOT] REGEXP_LIKE.
Example
SELECT REGEXP_SUBSTR('My mail address is my_mail@yahoo.com',
'(?i)[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,4}') EMAIL
;
Result
The email address my_mail@yahoo.com
.