REGEXP_SUBSTR

Purpose

This function returns a substring of the parameter string.

Usage notes

• For details and examples for regular expressions, see Regular expressions.
• 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).
• See also functions SUBSTR[ING], REGEXP_INSTR, REGEXP_REPLACE REGEXP_COUNT, and the predicate [NOT] REGEXP_LIKE.

Example

SELECT REGEXP_SUBSTR('My mail address is my_mail@test.com',
                     '(?i)[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,4}') EMAIL
;

Result

my_mail@test.com.