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.