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.


REPLACE(string1, old_chars, new_chars)



CHAR or VARCHAR string to be searched search


CHAR or VARCHAR string to replace.


New CHAR or VARCHAR string replacing the old_string.

Return Type


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


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

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)