Purpose
The REPLACE() function is used to replace substrings within a string. It searches for all occurrences of from_str in str and replaces them with to_str. If no from_str is found in str, the original string remains unchanged.
If any parameter in
REPLACE()isNULL, the result isNULL.The
REPLACE()function is case-sensitive, which means it considers the case of the strings. If you want to perform a case-insensitive replacement, you can useREPLACE(UPPER(str), UPPER(from_str), to_str)orREPLACE(LOWER(str), LOWER(from_str), to_str)to ensure the replacement is performed without considering case.The
REPLACE()function is multibyte-safe.
Syntax
REPLACE(str, from_str)
REPLACE(str, from_str, to_str)
Parameters
Parameter |
Required/Optional |
Description |
|---|---|---|
str |
Required | Original string to be replaced. |
from_str |
Required | The substring to be replaced. |
to_str |
Optional | The new string after replacement. If omitted, it is equivalent to an empty string, meaning the original string is deleted. from_str. |
Examples
- Example 1: The following query example uses the
REPLACEfunction to replace all substringsabc.withwwwin the original string.
obclient> SELECT REPLACE('abc.efg.gpg.nowdew.abc.dabc.e', 'abc.', 'www');
The return result is as follows:
+---------------------------------------------------------+
| REPLACE('abc.efg.gpg.nowdew.abc.dabc.e', 'abc.', 'www') |
+---------------------------------------------------------+
| wwwefg.gpg.nowdew.wwwdwwwe |
+---------------------------------------------------------+
1 row in set
- Example 2: The following query example uses the
REPLACEfunction to replaceNULLwithwwwin the original string. Since the substring to be replaced isNULL, the entire expression will returnNULL.
obclient> SELECT REPLACE('abc.efg.gpg.nowdew.abc.dabc.e', NULL, 'www');
The return result is as follows:
+-------------------------------------------------------+
| REPLACE('abc.efg.gpg.nowdew.abc.dabc.e', NULL, 'www') |
+-------------------------------------------------------+
| NULL |
+-------------------------------------------------------+
1 row in set
- Example 3: The following query example uses the
REPLACEfunction to replaceabc.withWWWin the original string. Since theREPLACE()function is case-sensitive, the result of the entire expression will beWWWefg.gpg.nowdew.WWWdWWWe.
obclient> SELECT REPLACE('abc.efg.gpg.nowdew.abc.dabc.e', 'abc.', 'WWW');
The return result is as follows:
+---------------------------------------------------------+
| REPLACE('abc.efg.gpg.nowdew.abc.dabc.e', 'abc.', 'WWW') |
+---------------------------------------------------------+
| WWWefg.gpg.nowdew.WWWdWWWe |
+---------------------------------------------------------+
1 row in set
- Example 4: In the following query example,
Hello Worldis converted to uppercaseHELLO WORLD, andworldis also converted to uppercase. Therefore, this query returns the resultHELLO Universebecause it replaces the uppercaseWORLDwithUniverse.
obclient> SELECT REPLACE(UPPER('Hello World'), UPPER('world'), 'Universe');
The return result is as follows:
+-----------------------------------------------------------+
| REPLACE(UPPER('Hello World'), UPPER('world'), 'Universe') |
+-----------------------------------------------------------+
| HELLO Universe |
+-----------------------------------------------------------+
1 row in set
MySQL compatibility
The behavior of the REPLACE() function varies across different versions: in MySQL 5.7, when the second parameter is an empty string ('') and the third parameter is NULL, the result is the first parameter; whereas in MySQL 8.0, the result is NULL.