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 offield_expression.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 replacement.
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 add a backslash in your regular expression, you must first escape it. For example, SELECT REGEXP_REPLACE('abc', 'b(.)', 'X\\1'); returns aXc. You can also use raw strings to remove one layer of escaping, for example SELECT 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 ABC123 BBC123 |
Summer Sale |
REGEXP_REPLACE(Campaign , '(.*):(.*)', '\\2 \\1') |
Sale:Summer Sale:Winter |
Summer Sale Winter Sale |