REGEXP_REPLACE
Stay organized with collections
Save and categorize content based on your preferences.
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 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'X1円');
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 |