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()returnsNULLwhen any input parameter isNULL.REPLACE()is case-sensitive, and therefore it takes into account the letter case of strings. If you want to make a case-insensitive replacement, useREPLACE(UPPER(str), UPPER(from_str), to_str)orREPLACE(LOWER(str), LOWER(from_str), to_str).REPLACE()is multi-byte safe.
Syntax
REPLACE(str, from_str, to_str)
Parameters
| Parameter | Required | Description |
|---|---|---|
str |
Yes | The original string in which a substring is to be replaced. |
from_str |
Yes | The substring to be replaced. |
to_str |
Yes | The new substring. |
Examples
- Example 1: Use
REPLACE()to replace allabc.in the original string withwww.
obclient> SELECT REPLACE('abc.efg.gpg.nowdew.abc.dabc.e', 'abc.', 'www');
```
The return result is as follows:
```sql
+---------------------------------------------------------+
| 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`.
```sql
obclient> SELECT REPLACE('abc.efg.gpg.nowdew.abc.dabc.e', NULL, 'www');
```
The return result is as follows:
```sql
+-------------------------------------------------------+
| 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`.
```sql
obclient> SELECT REPLACE('abc.efg.gpg.nowdew.abc.dabc.e', 'abc.', 'WWW');
```
The return result is as follows:
```sql
+---------------------------------------------------------+
| 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`.
```sql
obclient> SELECT REPLACE(UPPER('Hello World'), UPPER('world'), 'Universe');
```
The return result is as follows:
```sql
+-----------------------------------------------------------+
| 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.