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
REPLACE(string, old_chars, new_chars)
Arguments
- string
-
CHAR
orVARCHAR
string to be searched search - old_chars
-
CHAR
orVARCHAR
string to replace. - new_chars
-
New
CHAR
orVARCHAR
string replacing the old_string.
Return type
- VARCHAR
-
If either old_chars or new_chars is
NULL
, the return isNULL
.
Examples
The following example uses data from the CATEGORY table in the TICKIT sample database. For more information, see Sample database.
To convert the string Shows
to
Theatre
in the CATGROUP field, use the following example.
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 | +-------+----------+----------+