Sometimes web developers and database administrators need to find and replace text in entire table in MySQL. This is especially required if you move your website and need to change a domain name in URLs stored in your database. There are many third party tools and plugins that help you do this but you may not want to use them if it is just a one time thing. In this article, we will learn how to find and replace text in entire table in MySQL without using any third-party tools.
How to Find And Replace Text in Entire Table in MySQL
You can easily find and replace text in MySQL using UPDATE statement along with REPLACE() function. Here is the simple command for it.
UPDATE `table_name` SET `field_name` = replace(same_field_name, 'old_text', 'new_text')
In the above query, the replace() function replaces the old string in specified column first and then updates the column value with new string. Please note, replace() function will not update the column on its own, you need to use UPDATE statement for it.
If you have a table visitors(id, landing_page, referrer, date_created) and want to change domain example.com to mysite.com in column landing_page, you can do so with the following SQL query.
UPDATE visitors SET landing_page = replace(landing_page, 'example.com', 'mysite.com')
If you want to find and replace text only in certain rows of your table, you can specify the required condition to select those rows in WHERE clause as shown below.
UPDATE visitors SET landing_page = replace(landing_page, 'example.com', 'mysite.com') WHERE date_created>'2022-01-01';
This works when you need to update text in a single table but if you need to find and replace text from multiple tables, you need to run the above command separately for each table, or dump your database into a .sql file using MySQLdump command, find and replace text using text editor, and load it back to your database.
Here is the command to take backup of entire database.
mysqldump database_name > backup_file.sql
Open the above file in a text editor such as vi. Find and replace string in vi editor using steps mentioned here.
$ vi backup_file.sql
In this article, we have learnt how to find and replace text in entire table in MySQL. This is useful if you want to replace old text with new one, depending on your requirement.
Also read:
How to Take Backup of Single Database in MySQL
How to Show Indexes on Table or Database in MySQL
How to Set Global SQL Mode in MySQL
How to Check if Column is Empty or Null in MySQL
How to Create Please Wait Loading Animation in jQuery