Sample usage
Syntax
Parameters
X
- a field or expression that includes a field.regular_expression
- a regular expression that matches a portion ofX
.
replacement
- the text with which to replace the matched portion offield_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 |