REGEXP_REPLACE

Matches a pattern in the input string and replaces the first matched portion (from the left) with the specified replacement.

Syntax

REGEXP_REPLACE(<input>, <pattern>, <replacement>)

REGEXP_REPLACE_ALL

Matches a pattern in the input string and replaces all matched portions with the specified replacement.

Syntax

REGEXP_REPLACE_ALL(<input>, <pattern>, <replacement>)

Parameters

Parameter	Description	Supported input types
`<input>`	The string to search for a matching pattern	`TEXT`
`<pattern>`	An RE2 regular expression for matching with the string input.	`TEXT`
`<replacement>`	The string to replace the matching pattern found in the input. This argument can include the following special sequences: * `\&` - To indicate that the substring matching the entire pattern should be inserted. * `\n` - Where n is a digit from 1 to 9, to indicate that the substring matching the n’th capturing group (parenthesized subexpression) of the pattern should be inserted. If pattern doesn’t have n capturing groups, the `\n` is ignored. * `\\` - results in a single <br/>* `\c` - Specifies for any other character, c results in the same sequence \c Note, that for string literals the above escaping rules apply after string literals escaping rules for `\`. See examples below.	`TEXT`

If any of the arguments to these functions is NULL, the return value is NULL.

Return Type

TEXT

Examples

Replace first occurrence of ! with !!!:

SELECT REGEXP_REPLACE('Hello, world!', '!', '!!!');

regexp_replace text
Hello, world!!!

Rows: 1Execution time: 5.23ms

Remove leading and trailing spaces:

SELECT REGEXP_REPLACE_ALL('     Hello world ! ', '^[ ]+|[ ]+$', '');

regexp_replace_all text
Hello world !

Rows: 1Execution time: 5.17ms

Duplicate every character:

SELECT REGEXP_REPLACE_ALL('Hello, World!', '.', '\&\&');

regexp_replace_all text
HHeelllloo,, WWoorrlldd!!

Rows: 1Execution time: 5.53ms

Mask email address by leaving first character only (Note: this is for illustrative purposes only, the email matching pattern is too simplistic):

SELECT REGEXP_REPLACE(email, '(\w)[\w\.]+@([\w]+\.)+([\w]+)', '\1***@\2\3') AS masked_email
FROM UNNEST([
  'matt123@hotmail.com',
  'joe.doe@gmail.com',
  '12345@www.atg.wa.gov'
]) email
ORDER BY masked_email;

masked_email text
1***@wa.gov
j***@gmail.com
m***@hotmail.com

Rows: 3Execution time: 1.54ms

Convert dates into US format:

SELECT REGEXP_REPLACE(event_date::TEXT, '(\d{4})-(\d{2})-(\d{2})', '\2/\3/\1') AS converted_date
FROM UNNEST([
  DATE '1970-08-07',
  DATE '2000-04-22',
  DATE '2002-07-25',
  DATE '2010-11-11'
]) event_date
ORDER BY converted_date;

converted_date text
04/22/2000
07/25/2002
08/07/1970
11/11/2010

Rows: 4Execution time: 1.53ms

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

REGEXP_REPLACE_ALL

Syntax

Parameters

Return Type

Examples

​Syntax

​REGEXP_REPLACE_ALL

​Syntax

​Parameters

​Return Type

​Examples

Syntax

REGEXP_REPLACE_ALL

Syntax

Parameters

Return Type

Examples