MySQL Replace String Function

 MySQL Replace String Function

Summary: in this tutorial, we will show you how to use the MySQL REPLACE string function to replace a substring with another in a string.

Introduction to MySQL REPLACE string function

MySQL provides you with a useful string function called REPLACE that allows you to replace a string in a column of a table with a new string.

The syntax of the REPLACE the function is as follows:

REPLACE(str,old_string,new_string);

The REPLACE the function has three parameters. It replaces the old_string by the new_string in the string

Notice there is a statement also called REPLACE used to insert or update data. You should not confuse the REPLACE statement with the REPLACE string function.

The REPLACE the function is very handy to search and replace text in a table such as updating obsolete URLs, correcting a spelling mistake, etc.

The syntax of using the REPLACE function in an UPDATE statement is as follows:

UPDATE tbl_name 
SET 
    field_name = REPLACE(field_name,
        string_to_find,
        string_to_replace)
WHERE
    conditions;

Note that when searching for text to replace, MySQL uses the case-sensitive match to perform a search for a string to be replaced.

MySQL REPLACE string function example

For example, if you want to correct the spelling mistake in the products the table in the sample database, you use the REPLACE function as follows:

UPDATE products 
SET 
    productDescription = REPLACE(productDescription,
        'abuot',
        'about');

The query finds all occurrences of a spelling mistake abuot and replaces it with the correct word about in the productDescription column of the products table.

It is very important to note that in the REPLACE function, the first parameter is the column name without quotes (“). If you put the quotes to the field name like “field_name”, the query will update the content of that column to “field_name”, which is causing unexpected data loss.

The REPLACE the function does not support regular expression so if you need to replace a text string with a pattern you need to use MySQL user-defined function (UDF) from an external library, check it out here MySQL UDF with Regex