REGEXP_REPLACE

Replaces text within the target value.

Sample usage

REGEXP_REPLACE(Campaign , '(Sale):(Summer)', '\\2 \\1')

Syntax

REGEXP_REPLACE(X, regular_expression, replacement)

Parameters

X - a field or expression that includes a field.
regular_expression - a regular expression that matches a portion of X.
replacement - the text with which to replace the matched portion of field_expression.

Returns

The REGEXP_REPLACE function returns Text values.

Notes

REGEXP_REPLACE returns text where all substrings of X that match regular_expression are replaced with X.

You can use backslashed-escaped digits (\1 to \9) within the replacement argument to insert text matching the corresponding parenthesized group in the regular_expression pattern. Use \0 to refer to the entire matching text.

To search for a literal backslash in your regular expression, you must escape the literal backslash with a second backslash. For example:

REGEXP_REPLACE('abc', 'b(.)', 'X\\1');

returns aXc.

You can also use raw strings to remove one layer of escaping. For example:

REGEXP_REPLACE('abc', 'b(.)', r'X\1');

The REGEXP_REPLACE function only replaces non-overlapping matches. For example, replacing ana within banana results in only one replacement, not two.

Examples

Example formula	Input	Output
`REGEXP_REPLACE(LOWER(Campaign), ".bc123", "Summer Sale")`	abc123	Summer Sale
`REGEXP_REPLACE(LOWER(Campaign), ".bc123", "Summer Sale")`	BBC123	Summer Sale
`REGEXP_REPLACE(Campaign , '(.):(.)', '\\2 \\1')`	Sale:Summer	Summer Sale
`REGEXP_REPLACE(Campaign , '(.):(.)', '\\2 \\1')`	Sale:Winter	Winter Sale

Related resources

Was this helpful?

How can we improve it?