How REPLACE() Works in MariaDB

In MariaDB, REPLACE() is a built-in string function that allows you to replace a part of a string with another string.

The function accepts three arguments: the string, the substring to replace, and the string to replace the substring with.

Syntax

The syntax goes like this:

REPLACE(str,from_str,to_str)

Where str is the string, and from_str is the substring to replace, and to_str is the string to replace that substring with.

Example

Here’s a basic example:

SELECT REPLACE('Black dog', 'dog', 'cat');

Result:

+------------------------------------+
| REPLACE('Black dog', 'dog', 'cat') |
+------------------------------------+
| Black cat                          |
+------------------------------------+

In this case we replaced the substring dog with cat.

Multiple Matches

If the string to be replaced occurs multiple times within the string, all occurrences are replaced:

SELECT REPLACE('Black dogs and white dogs', 'dog', 'cat');

Result:

+----------------------------------------------------+
| REPLACE('Black dogs and white dogs', 'dog', 'cat') |
+----------------------------------------------------+
| Black cats and white cats                          |
+----------------------------------------------------+

No Match

If the substring doesn’t occur in the string, REPLACE() returns the string unchanged:

SELECT REPLACE('Black dog', 'horse', 'cat');

Result:

+--------------------------------------+
| REPLACE('Black dog', 'horse', 'cat') |
+--------------------------------------+
| Black dog                            |
+--------------------------------------+

Case Sensitivity

The REPLACE() function performs a case-sensitive match:

SELECT REPLACE('Black dog', 'Dog', 'Cat');

Result:

+------------------------------------+
| REPLACE('Black dog', 'Dog', 'Cat') |
+------------------------------------+
| Black dog                          |
+------------------------------------+

In this example, the case didn’t match, and so nothing was replaced.

Empty Strings

Here’s what happens when an empty string is passed for each given argument:

SELECT 
    REPLACE('', 'dog', 'cat') AS "1",
    REPLACE('Black dog', '', 'cat') AS "2",
    REPLACE('Black dog', 'dog', '') AS "3";

Result:

+------+-----------+--------+
| 1    | 2         | 3      |
+------+-----------+--------+
|      | Black dog | Black  |
+------+-----------+--------+

So in this case:

Passing an empty string for the initial string returns an empty string.
Passing an empty string for the second argument returns the original string.
Passing an empty string for the third argument removes the string to be replaced from the string.

Space Character

An empty string is not the same as the space character.

Here’s what happens when we change the empty string to a space:

SELECT 
    REPLACE(' ', 'dog', 'cat') AS "1",
    REPLACE('Black dog', ' ', 'cat') AS "2",
    REPLACE('Black dog', 'dog', ' ') AS "3";

Result:

+------+-------------+---------+
| 1    | 2           | 3       |
+------+-------------+---------+
|      | Blackcatdog | Black   |
+------+-------------+---------+

Therefore, if the string is nothing but a space, then we can replace that with another string:

SELECT REPLACE(' ', ' ', 'cat');

Result:

+--------------------------+
| REPLACE(' ', ' ', 'cat') |
+--------------------------+
| cat                      |
+--------------------------+

Null Arguments

Providing null results in null:

SELECT 
    REPLACE(null, 'dog', 'cat') AS "1",
    REPLACE('Black dog', null, 'cat') AS "2",
    REPLACE('Black dog', 'dog', null) AS "3";

Result:

+------+------+------+
| 1    | 2    | 3    |
+------+------+------+
| NULL | NULL | NULL |
+------+------+------+

Missing Argument

Calling REPLACE() with the wrong number of arguments, or without passing any arguments results in an error:

SELECT REPLACE();

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1