MariaDB REPLACE() Function

In MariaDB, REPLACE() is a built-in string function that will replace all occurrences of a substring in a string with a new substring.

The MariaDB REPLACE() function performs case-sensitive searches.

MariaDB REPLACE() Syntax

Here is the syntax of the MariaDB REPLACE() function:

REPLACE(str, from_str, to_str)

Parameters

str

Required. The original string.

from_str

Required. The substring to be replaced.

to_str

Required. The new substring to replace.

If you provide the wrong number of parameters, MariaDB will report an error: 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.

Return value

The MariaDB REPLACE(str, from_str, to_str) function returns str with all are from_str replaced with to_str.

If any of the arguments is NULL, the REPLACE() function will return NULL.

MariaDB REPLACE() Examples

Basic example

SELECT REPLACE('Hi Alice', 'Hi', 'Hello');

Output:

+------------------------------------+
| REPLACE('Hi Alice', 'Hi', 'Hello') |
+------------------------------------+
| Hello Alice                        |
+------------------------------------+

In this example, we replaced Hi with Hello.

Case sensitive

The MariaDB REPLACE() function performs case-sensitive searches.

SELECT REPLACE('Hi Alice', 'hi', 'Hello');

Output:

+------------------------------------+
| REPLACE('Hi Alice', 'hi', 'Hello') |
+------------------------------------+
| Hi Alice                           |
+------------------------------------+

In this example, because hi is lowercase, and it does not match Hi and is not replaced.

Multiple matches

The MariaDB REPLACE() function will replace all the matches, as follows:

SELECT REPLACE('A small dog and a big dog', 'dog', 'cat');

Output:

+----------------------------------------------------+
| REPLACE('A small dog and a big dog', 'dog', 'cat') |
+----------------------------------------------------+
| A small cat and a big cat                          |
+----------------------------------------------------+

Conclusion

In MariaDB, REPLACE() is a built-in string function that will replace all occurrences of a substring in a string with a new substring.