REPLACE

Purpose

REPLACE() replaces a substring in a string. The function locates all occurrences of the from_str substring in the str string and replaces them with the value of to_str. If the function does not find any occurrences of from_str in str, the string remains unchanged.

• REPLACE() returns NULL when any input parameter is NULL.

• REPLACE() is case-sensitive, and therefore it takes into account the letter case of strings. If you want to make a case-insensitive replacement, use REPLACE(UPPER(str), UPPER(from_str), to_str) or REPLACE(LOWER(str), LOWER(from_str), to_str).

• REPLACE() is multi-byte safe.

Syntax

REPLACE(str, from_str, to_str)

Parameters

Examples

• Example 1: Use REPLACE() to replace all abc. in the original string with www.

  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: Use REPLACE() to replace NULL in the original string with www. Since the substring to be replaced is NULL, the return result is NULL.

  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: Use REPLACE() to replace abc. in the original string with WWW. Since REPLACE() is case-sensitive, the return result is WWWefg.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: Convert Hello World to HELLO WORLD, and world to WORLD. Use REPLACE() to replace WORLD with Universe. The return result is HELLO Universe.

  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
  

Compatibility with MySQL

REPLACE() in MySQL behaves differently in different versions. In MySQL 5.7, when the second parameter is an empty string ('') and the third parameter is NULL, the function returns the first parameter, while in MySQL 8.0, the return result in this case is NULL. OceanBase Database V4.2.1 and earlier versions are compatible with the function behavior in MySQL 8.0.