REGEXP_REPLACE

Reference material for REGEXP_REPLACE functions

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([
  '[e-mail]',
  '[e-mail]',
  '[e-mail]'
]) 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