REPLACE

Description

The REPLACE function is used to replace all occurrences of a specified substring in a string with a new substring. This function replaces all matching instances of the substring in the string, performing a global replace operation.

Difference from REPLACE_EMPTY function:

• REPLACE() replaces the specified substring, including empty strings
• REPLACE_EMPTY() is specifically used to replace empty values or empty strings with a specified value

Syntax

REPLACE(<str>, <old>, <new>)

Parameters

Parameter	Description
<str>	The source string where replacement will occur. Type: VARCHAR
<old>	The target substring to be replaced. If not found in str, no replacement occurs. Type: VARCHAR
<new>	The new substring used to replace old. Type: VARCHAR

Return Value

Returns VARCHAR type, representing the new string after replacement.

Replacement rules:

• Replaces all matching old substrings in the string
• Replacement is case-sensitive
• If old is an empty string, returns the original string (no operation performed)
• If new is an empty string, effectively deletes all matching old substrings

Special cases:

• If any parameter is NULL, returns NULL
• If str is an empty string, returns an empty string
• If old is an empty string, returns the original str (no replacement)
• If old is not found in str, returns the original str

Examples

1. Basic replacement operation

SELECT REPLACE('hello world', 'world', 'universe');

+---------------------------------------------+
| REPLACE('hello world', 'world', 'universe') |
+---------------------------------------------+
| hello universe                              |
+---------------------------------------------+

2. Replace multiple matches

SELECT REPLACE('apple apple apple', 'apple', 'orange');

+------------------------------------------------+
| REPLACE('apple apple apple', 'apple', 'orange') |
+------------------------------------------------+
| orange orange orange                           |
+------------------------------------------------+

3. Delete substring (replace with empty string)

SELECT REPLACE('banana', 'a', '');

+---------------------------+
| REPLACE('banana', 'a', '') |
+---------------------------+
| bnn                       |
+---------------------------+

4. NULL value handling

SELECT REPLACE(NULL, 'old', 'new'), REPLACE('test', NULL, 'new'), REPLACE('test', 'old', NULL);

+------------------------------+------------------------------+------------------------------+
| REPLACE(NULL, 'old', 'new')  | REPLACE('test', NULL, 'new') | REPLACE('test', 'old', NULL) |
+------------------------------+------------------------------+------------------------------+
| NULL                         | NULL                         | NULL                         |
+------------------------------+------------------------------+------------------------------+

5. UTF-8 character replacement

SELECT REPLACE('ṭṛì ḍḍumai test ṭṛì ḍḍumannàri', 'ṭṛì', 'replaced');

+-----------------------------------------------------------+
| REPLACE('ṭṛì ḍḍumai test ṭṛì ḍḍumannàri', 'ṭṛì', 'replaced') |
+-----------------------------------------------------------+
| replaced ḍḍumai test replaced ḍḍumannàri                  |
+-----------------------------------------------------------+

6. Empty-string edge cases — empty str returns empty; empty old returns str unchanged; empty new deletes every match

SELECT REPLACE('', 'old', 'new'), REPLACE('test', '', 'new'), REPLACE('test', 'old', '');

+---------------------------+----------------------------+----------------------------+
| REPLACE('', 'old', 'new') | REPLACE('test', '', 'new') | REPLACE('test', 'old', '') |
+---------------------------+----------------------------+----------------------------+
|                           | test                       | test                       |
+---------------------------+----------------------------+----------------------------+

7. Replacement is case-sensitive (only lowercase hello matches)

SELECT REPLACE('Hello HELLO hello', 'hello', 'hi');

+---------------------------------------------+
| REPLACE('Hello HELLO hello', 'hello', 'hi') |
+---------------------------------------------+
| Hello HELLO hi                              |
+---------------------------------------------+

8. old not present — returns str unchanged

SELECT REPLACE('hello world', 'xyz', 'abc');

+--------------------------------------+
| REPLACE('hello world', 'xyz', 'abc') |
+--------------------------------------+
| hello world                          |
+--------------------------------------+

9. Overlap-free repeated match

SELECT REPLACE('123123123', '123', 'ABC');

+------------------------------------+
| REPLACE('123123123', '123', 'ABC') |
+------------------------------------+
| ABCABCABC                          |
+------------------------------------+