REGEXP_REPLACE

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

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

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;