How To Find And Replaced In Your WordPress Database
Sometimes you just need to change a word or a string of characters throughout your WordPress installation. And that text isn't always in the post content. Most of the information of WordPress resides in the database in hundreds of fields and a dozen tables. In this tutorial, I'll show you how to find and replace text in WordPress via the database - both manually and through a plugin.
A short while ago, I was migrating a client's website to https, and was forced to use these methods to change all references from "http" to "https" in the database to avoid the dreaded "mixed content" error.
If you read some guides on the Internet, you'll see that all of them recommend going into phpMyAdmin via cPanel and go through each table and each field.
Specifically, they'll tell you to run this query - Don't do this!:
You can get a list of all your tables from phpMyAdmin by expanding the database like this:
However, I wouldn't recommend this approach at all. It's too tedious. You have to iterate through each and every relevant field of each and every table. Combine that with the danger of typos, and something going wrong, and it's a recipe for disaster.
In my experience, it's far better to just download the database as an SQL file, make the changes in Notepad++, and reimport it.
To export your table, check out my earlier tutorial on how to do this. Once you have the SQL file, take a backup. Then use a text editor like Notepad++ (don't use vanilla Notepad). You can download it here. Now just open SQL in Notepad++, and press Ctrl+H to open the replace dialogue box and select the "Mark" tab:
This will allow you to preview all the locations of your old text. Once you're sure, you can replace them all through the "Replace" tab or on a case by case basis.
Once that's done, save your SQL file and reimport the database. Tada! Your text has all been replaced.
If you don't want to go directly through the WordPress database because you think (correctly) that it's dangerous, using a plugin in the other option. My recommendation here is the Better Search Replace plugin. It's easy to use and has worked great for me in the past.
After installation, you can find it in the "Tools" section of your WordPress dashboard as shown here:
Now just enter in the text you want to find and the text you want to change it to. Also, select all tables in the list box - or restrict it to only a subset of the tables if that's what you want.
To start with, click the checkbox labeled "Dry Run". This will run the query without actually changing anything, so you can see what's being replaced ahead of time.
When the query runs, you can click the results to get an overview of the changes:
If you're satisfied with what you see, you can run the query again, but this time uncheck "Dry Run" and the plugin will replace all the occurrences of the string for real-time this time around. And you're done! This one of those plugins that you remove as soon as your job is done since it's unlikely that you'll be doing this frequently.
These two methods should help you find and replace text in the database. The first method is general advice for ANY MySQL database, whereas the second is WordPress specific.
A short while ago, I was migrating a client's website to https, and was forced to use these methods to change all references from "http" to "https" in the database to avoid the dreaded "mixed content" error.
Method 1: Manually Replacing Database Text
If you read some guides on the Internet, you'll see that all of them recommend going into phpMyAdmin via cPanel and go through each table and each field.
Specifically, they'll tell you to run this query - Don't do this!:
UPDATE `tablename` SET `fieldname` = replace(samefield_name, oldtext', 'newtext')You can get a list of all your tables from phpMyAdmin by expanding the database like this:
However, I wouldn't recommend this approach at all. It's too tedious. You have to iterate through each and every relevant field of each and every table. Combine that with the danger of typos, and something going wrong, and it's a recipe for disaster.
In my experience, it's far better to just download the database as an SQL file, make the changes in Notepad++, and reimport it.
To export your table, check out my earlier tutorial on how to do this. Once you have the SQL file, take a backup. Then use a text editor like Notepad++ (don't use vanilla Notepad). You can download it here. Now just open SQL in Notepad++, and press Ctrl+H to open the replace dialogue box and select the "Mark" tab:
This will allow you to preview all the locations of your old text. Once you're sure, you can replace them all through the "Replace" tab or on a case by case basis.
Once that's done, save your SQL file and reimport the database. Tada! Your text has all been replaced.
Method 2: Using a Plugin
If you don't want to go directly through the WordPress database because you think (correctly) that it's dangerous, using a plugin in the other option. My recommendation here is the Better Search Replace plugin. It's easy to use and has worked great for me in the past.
After installation, you can find it in the "Tools" section of your WordPress dashboard as shown here:
Now just enter in the text you want to find and the text you want to change it to. Also, select all tables in the list box - or restrict it to only a subset of the tables if that's what you want.
To start with, click the checkbox labeled "Dry Run". This will run the query without actually changing anything, so you can see what's being replaced ahead of time.
When the query runs, you can click the results to get an overview of the changes:
If you're satisfied with what you see, you can run the query again, but this time uncheck "Dry Run" and the plugin will replace all the occurrences of the string for real-time this time around. And you're done! This one of those plugins that you remove as soon as your job is done since it's unlikely that you'll be doing this frequently.
These two methods should help you find and replace text in the database. The first method is general advice for ANY MySQL database, whereas the second is WordPress specific.
Updated on: 10/10/2024
Thank you!