REPLACE

Purpose

The REPLACE() function replaces all occurrences of a substring in a string. This function searches for all instances 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 is NULL.

• The REPLACE() function is case-sensitive, meaning it considers the case of the strings. If you want 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 performed without considering the case.

• The REPLACE() function is multibyte-safe.

Syntax

REPLACE(str, from_str, to_str)

Parameters

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.

obclient> 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

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

obclient> 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

• Example 3: The following query example uses the REPLACE function to replace the substring 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.

obclient> 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

• 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, as it replaces the uppercase WORLD with Universe.

obclient> 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

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 will be the first parameter. In MySQL 8.0, the result will be NULL. OceanBase Database 4.2.1 and earlier versions are compatible with this behavior of MySQL 8.0.