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
-
CHARorVARCHARstring to be searched search - old_chars
-
CHARorVARCHARstring to replace. - new_chars
-
New
CHARorVARCHARstring 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 |
+-------+----------+----------+