MySQL Replace Function

This guide looks at the MySQL replace function that allows us to replace a string in a table column with a new string.

The replace function is simple and this guide will be a short one. Nonetheless, the replace function can come in handy when you need to update or change values in a table such as broken URL.

NOTE: Do not confuse the replace function with the replace statement used to update data in a table.

Basic Usage

The general syntax for this function is simple:

REPLACE(str, old_str, new_str);

A standard use case of the REPLACE function is when used with the UPDATE clause. The general syntax for this is:

UPDATE tbl_name SET field = REPLACE(field, old_str, new_str) WHERE [condition];

Example Use Cases

Below is an example on how to use the MySQL replace function:

NOTE: The replace function does not support RegEx and is hence not illustrated in this tutorial.

Consider the query below:

UPDATE sakila.film SET title = REPLACE("title", "DRAGON SQUAD", "Dragonfly Squad");

The above will find the string specified and replace it with the new string. Since there is only one iteration of the set string, only one value is changed.

Conclusion

This quick guide illustrated how to use MySQL replace function to replace a string with a new string in a database.

from Linux Hint https://ift.tt/3eVwER1