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.
If either old_chars or new_chars is NULL, the return is NULL.
The following example converts the string
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)