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

REPLACE Function

Replaces all occurrences of a set of characters within an existing string with other specified characters.

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

Syntax

Copy
REPLACE(string1, old_chars, new_chars)

Arguments

string

CHAR or VARCHAR string to be searched search

old_chars

CHAR or VARCHAR string to replace.

new_chars

New CHAR or VARCHAR string replacing the old_string.

Return Type

VARCHAR

If either old_chars or new_chars is NULL, the return is NULL.

Examples

The following example converts the string Shows to Theatre in the CATGROUP field:

Copy
select catid, catgroup, replace(catgroup, 'Shows', 'Theatre') from category order by 1,2,3; catid | catgroup | replace -------+----------+---------- 1 | Sports | Sports 2 | Sports | Sports 3 | Sports | Sports 4 | Sports | Sports 5 | Sports | Sports 6 | Shows | Theatre 7 | Shows | Theatre 8 | Shows | Theatre 9 | Concerts | Concerts 10 | Concerts | Concerts 11 | Concerts | Concerts (11 rows)