If you need to replace a substring with another string in MariaDB, here are two approaches that you can use.

The REPLACE() Function

In MariaDB, the REPLACE() function is designed specifically to replace a substring within another string.

You provide three arguments when calling the function. These are the string, the substring, and the replacement string.

Example:

SELECT REPLACE('My dog likes to dig holes', 'dog', 'cat');

Result:

+----------------------------------------------------+
| REPLACE('My dog likes to dig holes', 'dog', 'cat') |
+----------------------------------------------------+
| My cat likes to dig holes                          |
+----------------------------------------------------+

In this case we replaced the substring dog with cat.

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                          |
+----------------------------------------------------+

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

See How REPLACE() Works in MariaDB for more examples.

The REGEXP_REPLACE() Function

The REGEXP_REPLACE() function is similar to the REPLACE() function, except that it allows you to do pattern matching using regular expressions.

This makes REGEXP_REPLACE() more powerful than REPLACE(), as you can match against parts of a string to replace substrings that would be harder or impossible to match when just using REPLACE().

Example:

SELECT REGEXP_REPLACE('My cat has cuts', 'c.t', 'dog');

Result:

+-------------------------------------------------+
| REGEXP_REPLACE('My cat has cuts', 'c.t', 'dog') |
+-------------------------------------------------+
| My dog has dogs                                 |
+-------------------------------------------------+

Regular expressions can be very powerful, and this example uses a very simple example. In order to use REGEXP_REPLACE() effectively, you’ll need to know the correct pattern to use for the desired outcome.

It’s also possible to provide the full literal string as the pattern, just like you’d use with the REPLACE() function.

Therefore, we could rewrite the first example on this page to use REGEXP_REPLACE() instead of REPLACE().

Here’s an example that runs them side by side to illustrate what I mean:

SELECT 
    REPLACE('My dog likes to dig holes', 'dog', 'cat') AS "REPLACE()",
    REGEXP_REPLACE('My dog likes to dig holes', 'dog', 'cat') AS "REGEXP_REPLACE()";

Result:

+---------------------------+---------------------------+
| REPLACE()                 | REGEXP_REPLACE()          |
+---------------------------+---------------------------+
| My cat likes to dig holes | My cat likes to dig holes |
+---------------------------+---------------------------+

Also, the REGEXP_REPLACE() function follows the case sensitivity rules of the effective collation. Matching is performed case insensitively for case insensitive collations, and case sensitively for case sensitive collations and for binary data. However, the collation case sensitivity can be overridden using the (?i) and (?-i) PCRE flags.

See How REGEXP_REPLACE() Works in MariaDB for examples of case sensitivity and more.