At some point, you’ll likely need to crawl your entire website to find a specific term and replace it with something else. If the term only appears once it may be a simple task, but for something more prevalent throughout your entire site, you could be looking for potentially hundreds of needles in a haystack.
Fortunately, WordPress contains all of your website’s data within a specific database. In very simple terms, you have the ability to look through the information in a much more efficient manner than combing through your website manually, thanks to the MySQL ‘find and replace’ feature.
In this article, we’re going to talk a bit more about what databases are and how they work. We’ll also explain why you might need to replace terms within your database, then teach you two ways to do it. Let’s get to work!
Table of Contents
An Introduction to the MySQL Find and Replace Feature
Databases are compilations of ‘tables’ that contain large sets of information. Each time someone logs into your website, their credentials are checked against a specific database entry. Technically, you could store the information in simple text files, but it’s not efficient. Instead, databases enable you to keep the information organized in a way that makes it easy to access when you (and WordPress) need to. Plus, you can encrypt specific values within a database and make them secure. In contrast, with text files you’d need to encrypt the entire thing, including values that don’t need to be hidden.
One reason why databases are so efficient is due to the multiple management systems available to create and maintain them. Our favorite is My Structured Query Language (MySQL), which is an open-source platform based on SQL. In short, SQL is a language that enables you to access, edit, and manage the content in your databases more efficiently than doing so manually.
Because most modern websites use databases, it’s important to learn how to navigate around them. For example, WordPress creates a unique database for each new site to store critical information such as your content, user data, and much more. This way, the platform can just pull the information it needs from its MySQL database whenever it’s required. More importantly, using SQL enables you to search through your database and replace any values you want through simple commands. Here are some situations where this skill can come in handy:
- Updating your domain to a new one. If you ever decide to change your domain, you’ll need to update your internal links so they point to the new one. It’s possible to do this manually, but you can accomplish the same thing by updating your site’s database.
- Removing links to a specific site from your pages. If one of the sites you link to from your pages becomes broken, you’ll want to remove those references so visitors don’t get confused. Much like our last example, updating your database is the simplest way to do it.
There are, of course, plenty of other situations where searching for and replacing terms within your database is handy. However, mastering the basics is a great way to learn more about how databases work firsthand. For example, this post will teach you the basics of how to use SQL queries, which are simple commands you can use to interact with your database. It’s a great skill to have in your repertoire if you’re interested in getting into web development at some point.
How to Use the Search & Replace Plugin to Find and Replace WordPress Database Elements
The Search & Replace plugin is a tool that enables you to look within your database for specific values and – as you might imagine – replace them. It supports practically any type of value, and usually takes only seconds for the entire process to run its course.
There are plenty of WordPress tools to fulfill this function. However, Search & Replace stands out because it also enables you to back up your database. Backups are critical before making any significant changes to your website. Including this feature within the plugin makes it more likely you’ll create a backup, and it’s a thoughtful touch.
To use the plugin, access your WordPress dashboard and go to the Plugins tab. Once you’re in there, click on the Add New button at the top of the screen:
On the next page, look for the search bar to the right, above the list of available plugins. Here, type Search & Replace, and the plugin in question should show up as the first result. Remember to type the plugin’s name exactly as written – i.e. including the ampersand – otherwise you may find the wrong plugin:
Next, click the Install Now button next to the plugin’s name, and WordPress will download and set up the necessary files. When the plugin is ready to use, the Install Now button will change to a blue Activate one, and clicking it will finalize the installation process:
Before we talk about how to use the plugin’s search and replace feature, let’s back up your database. Head to Tools > Search & Replace within WordPress, then click the Create SQL File button:
WordPress will now create a backup of your database as an .sql file. When it’s ready for you to download, a success message with a link will appear at the top of the screen, which you should click:
If you ever want to reverse any of the changes you make while using the plugin, you can navigate to the SQL Import tab. Inside, you’ll find a prompt that reads Select SQL file to upload. Simply find the .sql backup you downloaded earlier and click on the Import SQL File button when you’re ready – the plugin will take care of the rest:
We recommend creating a new backup each time you use the plugin’s search and replace feature, no matter how minor the changes you make are. With that out of the way, let’s talk about the plugin’s main feature. To search for a specific term within your database and replace it, go to the Search & Replace tab. Inside, you’ll find two empty fields reading Search for and Replace with:
The process is simple – enter the term you want to look for into Search for, and the value you want to add into Replace with:
Next, choose the tables you want the plugin to search within – they should all contain clear nomenclature. For example, if you want to replace content within your posts, the table in question should include posts:
If you’re not sure where to look, feel free to highlight all the available tables. Finally, there’s an option called Dry Run at the bottom of the page, which is enabled by default. With this setting on, if you click on the Do Search & Replace button, the plugin will essentially run the query without making any changes:
When you’re ready, disable the Dry Run option before running the process again. Another success message means the process is complete! Before we move on, you’ll notice the Replace Domain URL tab:
This section works similarly to the Search & Replace tab, but solely for URLs within your content. It’s great for updating your internal links after changing your domain. Simply enter your new URL and click Do Replace Domain/URL – not before backing up your database, though!
How to Use MySQL to Find and Replace WordPress Database Elements
Although the plugin solution is good, you can also access and make changes to your database without installing any additional tools. In this case, we’re going to use a program called phpMyAdmin, which is built-into your 000Webhost control panel. This tool enables you to interact with your database using an easy-to-use interface, rather than the command line.
Once you’re in there, we’ll teach you how to use SQL queries to search and replace items within your database. As we discussed, SQL lets you use simple commands to make complex changes to your database in seconds. In other words, with the right commands, you can achieve the same results as a plugin.
However, you’ll need to get into phpMyAdmin and back up your database before you get to work. Log into your 000Webhost control panel and look for the Manage Database button at the top of the screen. Clicking on it will display a page with a list of all your databases:
If you’re running a single website, there should only be one item inside. There’s a button next to the DB Host section reading Manage. Click on it and select the phpMyAdmin option, which will open the tool in a new tab:
You’ll see a lot of options, but don’t worry – we’ll guide you exactly where you need to go. To your left, there should be a list of your existing databases. Look for the one with wp somewhere within its name and click on it. When it loads, you’ll see all the tables that make up your WordPress database:
There are several options laid out in a menu at the top of the screen, but the one we’re interested in at the moment is the Export tab. When the page loads, you can go right ahead and click on the Go button at the bottom of the screen. This will create a backup file of your database in sql format, which you can save locally in case something goes wrong:
To rollback your changes, access your database and go to the Import tab. You’ll see an option to find your .sql backup file and upload it from your computer:
This will restore your database to its previous state so you can run your search and replace queries again. With that out of the way, jump the SQL tab on phpMyAdmin’s main menu – you’ll type your queries into the empty field:
Now, we’re going to introduce you to your first SQL query. In this example, we’re looking for an URL we want to replace within the wp_posts table. More specifically, within the post_content field:
update wp_posts set post_content = replace(post_content,'http://www.oldurl.com',' http://www.newurl.com ');
This snippet instructs WordPress to replace the old URL with a new one. Naturally, you can replace those values with any text you want. Likewise, you can use the same command to look at different tables. For example, with this snippet, you could replace all the instances of the word “Hello” with “Greetings, my dear sir” within your comments:
update wp_comments set comment_content = replace(comment_content,'Hello',' Greetings, my dear sir ');
Sadly, SQL queries don’t enable you to target all of the tables within your database simultaneously. This means you’ll need to know the tables and fields you’ll want to target, but it’s not as complicated as you’d imagine. If you return to your database’s Structure tab, you’ll see a list of all the tables you can edit and their specific names under the Table heading:
Clicking on any of them will show you the unique fields within that table, which you’ll also need to jot down if you want to use SQL queries to target them:
The good news is in the vast majority of cases, your SQL queries will probably target the post_content field within the wp_posts table. Our first example of an SQL query is a good example of this, so by replacing the example values and clicking Go at the bottom-right of the SQL tab, you’ll be able to run similar queries on your own database.
Who Should Use Search and Replace Plugins Instead of SQL Queries
Technically, there’s no advantage to using a plugin over manual SQL queries for searching and replacing database values. However, using a plugin can save you time, especially if you’re unfamiliar with the names of the tables and fields you want to target.
As far as we’re concerned though, learning how to use SQL queries is a vital skill if you want to understand how databases work. After you run a few queries, you’ll have a decent understanding of the way WordPress stores its data, which can come in handy down the road. Plus, you’ll also have one less plugin to worry about maintaining on your back end.
Combing through an entire website to find specific terms or URLs is a fool’s errand unless you’re talking about a single page or two. Even then, you’re turning a seconds-long task into something that can eat up a good part of your day. While searching and replacing data may not be an everyday task, it’s still a useful skill to have in your arsenal.
To recap, the two best ways to find and replace search terms within a website involve going through your database either by:
Do you have any questions about how to search and replace elements within your WordPress database? Ask away in the comments section below!