REGEXP_REPLACE

This function replaces the occurrences identified by occ of the strings or capturing groups identified by patt in expr with the repl string.

If no matches are found, the original expr is returned.

This function complies with the POSIX standard for regular expressions and the Unicode guidelines for regular expressions.

Syntax

REGEXP_REPLACE(expr, 
               patt [, repl, pos, occ, param])

Arguments

expr: Character sequence or text column to be searched, of data type STRING.

---

patt: Regular expression pattern of data type STRING.

---

repl (optional): Replacement string of data type STRING.

If repl = '' (empty string), the substring found by patt is deleted from expr. Default value is repl = '' (empty string).

repl can contain up to 500 backreferences to subexpressions in the form \n, where n is a number between 1 and 9. Backslashes "" must be escaped with "\".

---

pos (optional): Number of characters from the beginning from which the search for patt should start. Default value is pos = 1 (character 1 from the left).

---

occ (optional): Defines the occurrence of patt in expr to be replaced by repl. Default value occ = 0. All occurrences of patt in expr are replaced by repl.

---

param (optional): String of data type STRING in which additional parameters can be specified to influence the search for patt.

---

Supported parameters:

Parameter	Effect
c (default)	Case sensitive search: Case is considered in the search.
i	Case insensitive search: Case is ignored in the search.
m	Search in multiline mode. Metacharacters `^` mark the beginning and `$` the end of a line in expr. Without this parameter, multiline mode is disabled. `^` and `$` mark the beginning and end of the entire expr.
n	Causes the dot (.), which matches any character, to match the newline character. If this parameter is omitted, the dot does not match the newline character.
x	Causes whitespace to be ignored. By default, whitespace matches itself.

Usage Notes

• Capturing groups: patt can capture subexpressions of the pattern (capturing groups). A capturing group is a regular expression enclosed in parentheses ( ). Backreferences to the capturing groups have the format n. n is an integer referring to the matching instance of the capturing group.

• Round brackets ( ) and square brackets [ ] must be escaped with single escape characters to be interpreted as literal strings.

In the following example for result1, ( ) are treated as defining a capturing group and not as a literal string, and thus are not replaced by an empty string ''. In result2, however, "(Hessen)" is replaced by '':

SELECT regexp_replace('Frankfurt am Main (Hessen)','(|)','') as result1, 
       regexp_replace('Frankfurt am Main (Hessen)','\(|\)','') as result2 
;

+----------------------------+--------------------------+
| RESULT1                    | RESULT2                  |
+----------------------------+--------------------------+
| Frankfurt am Main (Hessen) | Frankfurt am Main Hessen |
+----------------------------+--------------------------+

Example 1 | Delete Multiple Characters

Delete all spaces (replace with an empty string).

select regexp_replace('Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed.', '( ){1,}','') as result;

+-------------------------------------------------------+
| RESULT                                                |
|-------------------------------------------------------|
| Loremipsumdolorsitamet,consetetursadipscingelitr,sed. |
+-------------------------------------------------------+

Example 2 | Replace Specific Occurrences of Strings

occ = 2: Replace the 2nd occurrence (1st occurrence remains unchanged) with repl starting from pos = 1 (search start position).

select regexp_replace('Gaudeamus igitur, Iuvenes dum sumus. Gaudeamus igitur, Iuvenes dum sumus.', 'Iuvenes','Omnes homines', 1, 2) as result;

+----------------------------------------------------------------------------------+
|  RESULT                                                                          |
|----------------------------------------------------------------------------------|
| Gaudeamus igitur, Iuvenes dum sumus. Gaudeamus igitur, Omnes homines dum sumus.  |
+----------------------------------------------------------------------------------+

Example 3 | Use Capturing Groups

Swap the positions of sentences 1 and 2.

select regexp_replace('Gaudeamus igitur, Omnes homines dum sumus. Gaudeamus igitur, Iuvenes dum sumus.','(.*\.) (.*\.)','\2 \1') as result;

+----------------------------------------------------------------------------------+
| RESULT                                                                           |
|----------------------------------------------------------------------------------|
| Gaudeamus igitur, Iuvenes dum sumus. Gaudeamus igitur, Omnes homines dum sumus.  |
+----------------------------------------------------------------------------------+

Example 4 | Use Capturing Groups (2)

Format a phone number.

SELECT
  REGEXP_REPLACE('0999.321.9876',
                 '([[:digit:]]{4})\.([[:digit:]]{3})\.([[:digit:]]{4})',
                 '(\1) \2-\3') result;

+------------------+
| RESULT           |   
+------------------+
| (0999) 321-9876  |
+------------------+

Example 5 | Replace Multiple Characters

Delete the first letter of each word.

select regexp_replace('Gaudeamus igitur, Omnes homines dum sumus. Gaudeamus igitur, Iuvenes dum sumus.','(\w)(\w+)','\2') as result;

+-----------------------------------------------------------------------+
| RESULT                                                                |
|-----------------------------------------------------------------------|
| audeamus gitur, mnes omines um umus. audeamus gitur, uvenes um umus.  |
+-----------------------------------------------------------------------+