{"id":507,"date":"2011-04-24T13:05:50","date_gmt":"2011-04-24T13:05:50","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=507"},"modified":"2020-04-11T02:02:41","modified_gmt":"2020-04-11T09:02:41","slug":"mysql-replace-function","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-string-functions\/mysql-replace-function\/","title":{"rendered":"MySQL Replace String Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, we will show you how to use MySQL REPLACE string function to replace a substring by another in a string.<\/p>\n\n\n\n

Introduction to MySQL REPLACE string function<\/h2>\n\n\n\n

MySQL provides you with a useful string function called REPLACE<\/code> that allows you to replace a string in a column of a table by a new string.<\/p>\n\n\n\n

The syntax of the REPLACE<\/code> function is as follows:<\/p>\n\n\n

REPLACE<\/span>(str<\/span>,old_string,new_string);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The REPLACE<\/code> function has three parameters. It replaces the old_string<\/code> by the new_string<\/code> in the string<\/code><\/p>\n\n\n\n
Notice there is a statement also called REPLACE<\/a> used to insert<\/a> or update<\/a> data. You should not confuse the REPLACE<\/code> statement with the REPLACE<\/code> string function.<\/p>\n\n\n\n
The REPLACE<\/code> function is very handy to search and replace text in a table such as updating obsolete URL, correcting a spelling mistake, etc.<\/p>\n\n\n\n
The syntax of using the REPLACE<\/code> function in an UPDATE statement<\/a> is as follows:<\/p>\n\n\n
UPDATE<\/span> tbl_name \nSET<\/span> \n    field_name = REPLACE<\/span>(field_name,\n        string_to_find,\n        string_to_replace)\nWHERE<\/span>\n    conditions;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that when searching for text to replace, MySQL uses the case-sensitive match to perform a search for a string to be replaced.<\/p>\n\n\n\n
MySQL REPLACE string function example<\/h2>\n\n\n\n
For example, if you want to correct the spelling mistake in the products<\/code> table in the sample database<\/a>, you use the REPLACE<\/code> function as follows:<\/p>\n\n\n
UPDATE<\/span> products \nSET<\/span> \n    productDescription = REPLACE<\/span>(productDescription,\n        'abuot'<\/span>,\n        'about'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The query finds all occurrences of a spelling mistake abuot<\/code> and replaces it by the correct word about in the productDescription<\/code> column of the products<\/code> table.<\/p>\n\n\n\n
It is very important to note that in the REPLACE<\/code> 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.<\/p>\n\n\n\n
\n\t
\n\t\t
Was this tutorial helpful? <\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
The REPLACE<\/code> function does not support regular expression<\/a> 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<\/a><\/p>\n