replace
Description
Replaces all occurrences of characters in a string with another string. This function performs a case-sensitive match when searching for pattern
.
This function is supported from v3.0.
Note: Prior to 3.0, this function was implemented as regexp_replace.
Syntax
VARCHAR replace(VARCHAR str, VARCHAR pattern, VARCHAR repl)
Parameters
-
str
: the original string. -
pattern
: the characters to replace. Note that this is not a regular expression. -
repl
: the string used to replace characters inpattern
.
Return value
Returns a string with the specified characters replaced.
If any argument is NULL, the result is NULL.
If no matching characters are found, the original string is returned.
Examples
-- Replace '.' in 'a.b.c' with '+'.
MySQL > SELECT replace('a.b.c', '.', '+');
+----------------------------+
| replace('a.b.c', '.', '+') |
+----------------------------+
| a+b+c |
+----------------------------+
-- No matching characters are found and the original string is returned.
MySQL > SELECT replace('a b c', '', '*');
+----------------------------+
| replace('a b c', '', '*') |
+----------------------------+
| a b c |
+----------------------------+
-- Replace 'like' with an empty string.
MySQL > SELECT replace('We like StarRocks', 'like', '');
+------------------------------------------+
| replace('We like StarRocks', 'like', '') |
+------------------------------------------+
| We StarRocks |
+------------------------------------------+
-- No matching characters are found and the original string is returned.
MySQL > SELECT replace('He is awesome', 'handsome', 'smart');
+-----------------------------------------------+
| replace('He is awesome', 'handsome', 'smart') |
+-----------------------------------------------+
| He is awesome |
+-----------------------------------------------+
keywords
REPLACE, replace