REGEXP_REPLACE

This function replaces the occurrences of pattern in a string by replace_string.

Syntax

regexp_replace::=

Usage Notes

  • For details and examples for regular expressions, refer to Regular Expressions section.
  • If pattern is NULL, string is returned.
  • If replace_string is omitted or NULL, the matches of pattern are deleted in the result.
  • In replace_string you can use captures by \1, \2, ..., \9 or \g<name> which are defined by pattern.
  • 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.

    • The occurrences do not overlap. Therefore, search of the second occurrence begins at the first character after the first occurrence. In case of 0 all occurrences are replaced (default). In case of a positive integer n, the n-th occurrence will be replaced.

  • For additional information, see functions REPLACE, REGEXP_INSTR, and REGEXP_SUBSTR, and the predicate [NOT] REGEXP_LIKE.

Example

Copy 
SELECT REGEXP_REPLACE( 
         'From: my_mail@yahoo.com',
         '(?i)^From: ([a-z0-9._%+-]+)@([a-z0-9.-]+\.[a-z]{2,4}$)',
         'Name: \1 - Domain: \2') REGEXP_REPLACE;