REGEXP_REPLACE ​

Replace instances of a pattern in the input with the value in the replacement.

Back references can be used within the replacement string to match expressions within capturing groups. Capturing groups can range from 1 to 9 (\1 to \9).

The entire match can be retrieved with \&.

Syntax ​

result = REGEXP_REPLACE(input, pattern, replacement [, start [, reference]] [, flags])

input value

Specify the value against which the regular expression is matched.

pattern value

Specify the regular expression to be matched against the input.

When the pattern matches, the matched string from the input will be replaced by the value specified in the replacement argument.

The pattern value must be a constant string and cannot be a column reference.

replacement value

Specify the value to substitute for each instance of the pattern.

start value

The position in the input from where the pattern match starts. The default start position is 1.

Yellowbrick does not support special meanings for negative start values.

The start values can be column references.

reference value

Specify which match of the pattern to replace.

To replace a specific instance of the pattern, use a reference value greater than 0 to indicate the occurrence. For example, a reference value of 1 would replace the first match of the pattern, a reference value of 2 would replace the second match of the pattern, and so on.

To replace all occurrences, omit a reference value or use a reference value of 0, both of which are interpreted similarly and result in a global replace.

The reference values can be column references.

flags value

Can be either i for case-insensitive or c for case-sensitive matching. Matching is case-sensitive by default.

The flags value must be a constant string and cannot be a column reference.

Return ​

If the pattern matches the input, the function applies the replacement and returns the result.

If the pattern doesn't match the input, the function returns the input string.

Example ​

premdb=> select season_name,REGEXP_REPLACE(season_name,'(\d{4,4})','year:\1', 1, 1) from season where numteams=22;
 season_name | regexp_replace
-------------+----------------
 1992-1993   | year:1992-1993
 1993-1994   | year:1993-1994
 1994-1995   | year:1994-1995
(3 rows)

Parent topic:SQL Operators and Pattern Matching Functions