regexp_replace() function

Syntax

varchar = regexp_replace(varchar input, varchar pattern, varchar 
replacement [, int start_pos [, int reference]] [, varchar flags]);
nvarchar = regexp_replace(nvarchar input, nvarchar pattern, nvarchar 
replacement [, int start_pos [, int reference]] [, varchar flags]);

The input value specifies the varchar or nvarchar value against which the regular expression is processed.

The pattern value specifies the regular expression. For a description of how to specify Perl compatible regular expression (PCRE) patterns for Unicode data, see any general PCRE documentation or web sources.

The replacement value specifies the value to substitute for each instance of the pattern. Within the replacement string, you can use backreferences to match on expressions inside a capturing group, as described in Backreferences.

The start_pos value specifies the character position in the pattern string at which to start the replacement. The default is 1.

The reference value specifies the instance of the pattern to replace. By default, the reference value is 0, which replaces all instances of the pattern with the replacement value. To replace only a specific instance of the pattern, specify a value greater than 0 (1 corresponds to the first reference, 2 to the second reference, 3 to the third reference, and so on).

For a description of the flags value, see flags argument.

Returns

Example

select regexp_replace('Red Yellow Blue Red Green 
Blue','Red','Orange');
             REGEXP_REPLACE
--------------------------------------
 Orange Yellow Blue Orange Green Blue
(1 row)

select regexp_replace('Red Yellow Blue Red Green Blue','Red','Orange', 1, 2);
          REGEXP_REPLACE
-----------------------------------
 Red Yellow Blue Orange Green Blue
(1 row)

Backreferences

You can use backreferences to match expressions inside a capturing group. A capturing group is a regular expression that is enclosed by parentheses, (), characters. Backreferences have the form \number, where number is 0 - 9, inclusive, which refer to the matching instance of the capturing group.

In the following example, the first instance of a capturing group is replaced with the replacement string:

select regexp_replace('Red Yellow Blue Red Green Blue','(Red)',
'Colors: \1', 1, 1);

             REGEXP_REPLACE
----------------------------------------
 Colors: Red Yellow Blue Red Green Blue
(1 row)

If you want to specify a forward slash (/) in the replacement string, escape the slash with a leading slash character, as shown in the following example:

select regexp_replace('Red Yellow Blue Red Green Blue','(Red)',
'\\Colors: \1', 1, 1);
             REGEXP_REPLACE
-----------------------------------------
 \Colors: Red Yellow Blue Red Green Blue
(1 row)

There is partial support for replacement strings that use a combination of backreferences and positive lookbehind. The function does not support a match when the lookbehind string in the lookbehind pattern occurs before the start_pos value. In the following example, the regular expression pattern is used to search for Red followed by Yellow with a start_pos of 4. In this case, even though the first Yellow appears at position 5 (after the start position), the match changes the second instance of Red Yellow in the input string because the first instance of the lookbehind string Red appears before start position 4.

select regexp_replace('Red Yellow Red Green Red Yellow Red Blue Red Yellow',
'(?<=Red )(Yellow)','INSERT \1',4,1);
                       REGEXP_REPLACE
------------------------------------------------------------
 Red Yellow Red Green Red INSERT Yellow Red Blue Red Yellow
(1 row)