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 to REGEXP_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
.