Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

REGEXP_REPLACE Function

Searches a string for a regular expression pattern and replaces every occurrence of the pattern with the specified string. REGEXP_REPLACE is similar to the REPLACE Function, but lets you search a string for a regular expression pattern. For more information about regular expressions, see POSIX Operators.

REGEXP_REPLACE is similar to the TRANSLATE Function and the REPLACE Function, except that TRANSLATE makes multiple single-character substitutions and REPLACE substitutes one entire string with another string, while REGEXP_REPLACE lets you search a string for a regular expression pattern.

Syntax

Copy
REGEXP_REPLACE ( source_string, pattern [, replace_string [ , position ] ] )

Arguments

source_string

A string expression, such as a column name, to be searched.

pattern

A string literal that represents a SQL standard regular expression pattern.

replace_string

A string expression, such as a column name, that will replace each occurrence of pattern. The default is an empty string ( "" ).

position

A positive integer that indicates the position within source_string to begin searching. The position is based on the number of characters, not bytes, so that multibyte characters are counted as single characters. The default is 1. If position is less than 1, the search begins at the first character of source_string. If position is greater than the number of characters in source_string, the result is source_string.

Return Type

VARCHAR

If either pattern or replace_string is NULL, the return is NULL.

Example

The following example deletes the @ and domain name from email addresses.

Copy
select email, regexp_replace( email, '@.*\\.(org|gov|com)$') from users limit 5; email | regexp_replace -----------------------------------+---------------- DonecFri@semperpretiumneque.com | DonecFri mk1wait@UniOfTech.org | mk1wait sed@redshiftemails.com | sed bunyung@integermath.gov | bunyung tomsupporter@galaticmess.org | tomsupporter

The following example selects URLs from the fictional WEBSITES table and replaces the domain names with this value: internal.company.com/

Copy
select url, regexp_replace(url, '^.*\\.[[:alpha:]]{3}/', 'internal.company.com/') from websites limit 4; url ----------------------------------------------------- | regexp_replace +----------------------------------------------------- example.com/cuisine/locations/home.html | internal.company.com/cuisine/locations/home.html anycompany.employersthere.com/employed/A/index.html | internal.company.com/employed/A/index.html example.gov/credentials/keys/public | internal.company.com/credentials/keys/public yourcompany.com/2014/Q1/summary.pdf | internal.company.com/2014/Q1/summary.pdf