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 field_expression.
  • 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 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

Was this helpful?
How can we improve it?
true
What's new in Data Studio

Learn about new features and recent changes.

Search
Clear search
Close search
Google apps
Main menu
Search Help Center
true
102097
false