Have you ever run into the issue where you have a fairly large MySQL table and you would like to replace a string on one or more of the columns?
Let’s say you change the name of your website and there is a particular column with rows that have the old domain name and you would like to change it into the new one. If you have a similar situation then this article will help.
There are actually a couple of ways that you can go about doing this. One way to do this is to export the entire database or just the table into a SQL file and do a regular find and replace with a text-editor of your choice. This is certainly the first way that came to mind when I came across this situation recently.
The only problem with the approach is that if you have a very huge database, then this can take a really long time to accomplish. Solving a problem like this shouldn’t take more that 10 minutes right? That’s what I thought.
Thankfully, MySQL comes with a string replace function which can be used for this exact problem. In this article we will be going over an example of using the REPLACE STRING function in MySQL.
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(str,old_string,new_string);
The REPLACE function has three parameters. It replaces the old_string by the new_string in the string
A very important thing to note here is that 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 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:
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 use
For example, if you want to correct the domain name scenario I was talking about in the first paragraph. In the table in the sample database table below:
---------------- |domain_names | ---------------- |example1.com | |example2.com | |example-2.com | |example3.com | ----------------
You can use the REPLACE function as follows:
UPDATE domainList
SET
domain_names = REPLACE(domain_names,
'example-2.com',
'example2.com');
The query finds all occurrences of the domain about example-2.com and replaces it by the correct value example2.com in the domain_names column of the domain list 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 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. I hope this has been helpful and this little tip will come in handy when you have to deal with such a situation.
