REPLACE()

Description

The REPLACE() function is used to replace substrings in a string. This function searches for all occurrences of from_str in str and replaces them with to_str. If no match is found for from_str in str, the original string remains unchanged.

• If any parameter in REPLACE() is NULL, the result will be NULL.

• The REPLACE() function is case-sensitive, meaning it considers the case of the strings. To perform a case-insensitive replacement, you can use REPLACE(UPPER(str), UPPER(from_str), to_str) or REPLACE(LOWER(str), LOWER(from_str), to_str) to ensure the replacement is case-insensitive.

• The REPLACE() function is multibyte-safe.

Syntax

REPLACE(str, from_str, to_str)

Parameters

Parameter	Required/Optional	Description
str	Required	The original string to be replaced.
from_str	Required	The substring to be replaced.
to_str	Required	The new string after replacement.

Examples

• Example 1: The following query example uses the REPLACE function to replace all occurrences of the substring abc. in the original string with www.

SELECT REPLACE('abc.efg.gpg.nowdew.abc.dabc.e', 'abc.', 'www');

The result is as follows:

+---------------------------------------------------------+
| REPLACE('abc.efg.gpg.nowdew.abc.dabc.e', 'abc.', 'www') |
+---------------------------------------------------------+
| wwwefg.gpg.nowdew.wwwdwwwe                              |
+---------------------------------------------------------+
1 row in set (0.001 sec)

• Example 2: The following query example uses the REPLACE function to replace NULL in the original string with www. Since the substring to be replaced is NULL, the result of the entire expression will be NULL.

SELECT REPLACE('abc.efg.gpg.nowdew.abc.dabc.e', NULL, 'www');

The result is as follows:

+-------------------------------------------------------+
| REPLACE('abc.efg.gpg.nowdew.abc.dabc.e', NULL, 'www') |
+-------------------------------------------------------+
| NULL                                                  |
+-------------------------------------------------------+
1 row in set (0.001 sec)

• Example 3: The following query example uses the REPLACE function to replace abc. in the original string with WWW. Since the REPLACE() function is case-sensitive, the result of the entire expression will be WWWefg.gpg.nowdew.WWWdWWWe.

SELECT REPLACE('abc.efg.gpg.nowdew.abc.dabc.e', 'abc.', 'WWW');

The result is as follows:

+---------------------------------------------------------+
| REPLACE('abc.efg.gpg.nowdew.abc.dabc.e', 'abc.', 'WWW') |
+---------------------------------------------------------+
| WWWefg.gpg.nowdew.WWWdWWWe                              |
+---------------------------------------------------------+
1 row in set (0.000 sec)

• Example 4: In the following query example, Hello World is converted to uppercase HELLO WORLD, and world is also converted to uppercase. Therefore, the query will return the result HELLO Universe, because it replaces the uppercase WORLD with Universe.

SELECT REPLACE(UPPER('Hello World'), UPPER('world'), 'Universe');

The result is as follows:

+-----------------------------------------------------------+
| REPLACE(UPPER('Hello World'), UPPER('world'), 'Universe') |
+-----------------------------------------------------------+
| HELLO Universe                                            |
+-----------------------------------------------------------+
1 row in set (0.001 sec)

MySQL Compatibility

The behavior of the REPLACE() function varies across different versions. In MySQL 5.7, if the second parameter is an empty string ('') and the third parameter is NULL, the result will be the first parameter. However, in MySQL 8.0, the result will be NULL. seekdb is compatible with this behavior of MySQL 8.0.