Summary: in this tutorial, we will show you how to use MySQL REPLACE string function to replace a substring by 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 by a new string.
The syntax of the
REPLACE function is as follows:
REPLACE function has three parameters. It replaces the
old_string by the
new_string in the
REPLACE function is very handy to search and replace text in a table such as updating obsolete URL, correcting a spelling mistake, etc.
The syntax of using the
REPLACE function in an UPDATE statement is as follows:
field_name = REPLACE(field_name,
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 table in the sample database, you use the
REPLACE function as follows:
productDescription = REPLACE(productDescription,
The query finds all occurrences of a spelling mistake
abuot and replaces it by the correct word about in the
productDescription column of the
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.
REPLACE function does not support regular expression so if you need to replace a text string by a pattern you need to use MySQL user-defined function (UDF) from external library, check it out here MySQL UDF with Regex