How to Easily Search and Replace Text in Your WordPress Database

As a WordPress site owner, you‘re responsible for managing not just the front-end content and appearance of your site, but also the underlying database powering everything behind the scenes. While you don‘t need to be a database expert to run a WordPress site, a basic understanding of how to perform key management tasks can save you a lot of time and headaches.

One essential database management task is knowing how to search and replace text across your entire WordPress database. There are many scenarios where you may need to do this:

  • Changing URLs when moving your site to a new domain
  • Adding or removing "www" from URLs
  • Updating URLs from HTTP to HTTPS after adding an SSL certificate
  • Renaming a product, category or term that appears in many posts and pages
  • Fixing a common misspelling or typo that appears across your site content

Manually updating the text in each location would be incredibly time-consuming, especially for a large site. Luckily, WordPress provides a few ways to automate a global find and replace. With the right method, you can update every instance of a word or phrase in just a few clicks.

In this guide, we‘ll walk through two methods for searching and replacing text in your WordPress database:

  1. Using a WordPress plugin
  2. Writing a custom MySQL query in phpMyAdmin

We‘ll cover step-by-step instructions, examples, and best practices so you can safely and effectively make database-wide text changes. Let‘s get started!

Method 1: Use a WordPress Plugin to Search and Replace Database Text

The simplest way to perform a search and replace on your WordPress database is by using a plugin. This requires no technical knowledge and can be done right from your WordPress dashboard.

There are a few reputable plugins that add global search and replace functionality:

Search & Replace

Search & Replace is a free plugin by Inpsyde that enables you to search and replace plain text, URLs, and serialized data in your WordPress database.

After installing and activating the plugin, go to Tools → Search & Replace to access the plugin settings.

Search and Replace plugin settings screen

To update URLs, enter the old URL in the "Search for" field and the new URL in the "Replace with" field, then click "Do Search & Replace".

For example, if you changed your site‘s URL from http://mysite.com to https://mysite.com, you would enter those values to update all instances of the old URL in the database to the new one.

Updating URLs with Search and Replace plugin

To replace other text like post content, titles, excerpts, comments, etc., switch to the "Search & Replace" tab.

Enter the word or phrase you want to find in the "Search for" field, and the replacement text in the "Replace with" field. You can select specific database tables to search, or leave "Select tables" blank to search all tables.

Importantly, you can also check the "Dry run" box to test the change before actually updating the database. This will show you a preview of all the instances that will be updated.

Previewing text updates with Search and Replace dry run

Once you‘ve confirmed the changes, uncheck "Dry run" and click "Do Search & Replace" to perform the replacement. All matching instances in the selected database tables will be updated.

One standout feature of this plugin is the ability to create a database backup before making changes. In the "Backup Database" tab, simply enter a name for the backup file and click "Create SQL File". You can then download the backup for safekeeping or to restore if something goes wrong.

Better Search Replace

Better Search Replace is another free plugin, created by the developers at Delicious Brains, that makes it easy to update text across your WordPress database.

One key feature is the ability to perform a "dry run" to see exactly how many instances would be updated across each database table before finalizing the changes. This preview helps you double check that the correct text will be updated.

Previewing updates with Better Search Replace dry run

Better Search Replace also supports searching and replacing text in serialized data, and provides an option to run a search/replace on all tables in a Multisite network at once.

While Better Search Replace doesn‘t have a built-in database backup feature like the Search & Replace plugin, taking a backup before running this plugin is still strongly recommended. You can create a database backup using a separate WordPress backup plugin.

Method 2: Search and Replace Database Text with a Custom MySQL Query

If you‘re comfortable working with databases and MySQL, you can perform a search and replace directly in phpMyAdmin with a custom query. This gives you full control over the process and doesn‘t require installing a plugin.

However, writing SQL queries is more technical and mistakes can easily break your site, so this method should only be used if you‘re confident in your database skills. Be sure to backup your database before proceeding.

To get started, log in to the control panel for your WordPress hosting account and open phpMyAdmin.

Find your WordPress database in the list on the left and click to open it. If you‘re not sure of the database name, you can find it in the wp-config.php file – look for the line that says:

define(‘DB_NAME‘, ‘your_database_name‘);

Once in phpMyAdmin, select the SQL tab from the top menu to open the SQL query editor.

phpMyAdmin SQL tab

Here‘s the basic syntax for a search and replace query in MySQL:

UPDATE table_name
SET column_name = REPLACE(column_name, ‘search_text‘, ‘replace_text‘)
WHERE column_name LIKE ‘%search_text%‘;

To break this down:

  • table_name is the name of the database table you want to update, like wp_posts or wp_postmeta
  • column_name is the name of the column within that table that contains the text you‘re searching for, like post_title or post_content
  • search_text is the exact word/phrase you want to find
  • replace_text is the new word/phrase you want to replace the search text with

For example, let‘s say you want to replace all instances of the phrase "Click here" with "Learn more" across all your posts. The query would look like this:

UPDATE wp_posts
SET post_content = REPLACE(post_content, ‘Click here‘, ‘Learn more‘)
WHERE post_content LIKE ‘%Click here%‘;

If you want to search and replace multiple variations of a word or phrase, you can run the query multiple times. For instance, to update both "Acme Widget" and "acme widget" to "Acme Gadget", you would run:

UPDATE wp_posts
SET post_content = REPLACE(post_content, ‘Acme Widget‘, ‘Acme Gadget‘)
WHERE post_content LIKE ‘%Acme Widget%‘;

UPDATE wp_posts
SET post_content = REPLACE(post_content, ‘acme widget‘, ‘Acme Gadget‘)
WHERE post_content LIKE ‘%acme widget%‘;

To update URLs, you‘ll need to search and replace in a few different tables, namely wp_posts, wp_postmeta, wp_comments, and wp_options. Run an update query on each of those tables, like so:

UPDATE wp_posts
SET post_content = REPLACE(post_content, ‘http://oldurl.com‘, ‘https://newurl.com‘);

UPDATE wp_postmeta
SET meta_value = REPLACE(meta_value, ‘http://oldurl.com‘,‘https://newurl.com‘);

UPDATE wp_comments
SET comment_content = REPLACE(comment_content, ‘http://oldurl.com‘, ‘https://newurl.com‘);

UPDATE wp_options
SET option_value = REPLACE(option_value, ‘http://oldurl.com‘, ‘https://newurl.com‘);

After writing your query, click the "Go" button to execute it. phpMyAdmin will display a success message with the number of rows affected.

Tips for Safely Searching and Replacing WordPress Database Text

No matter which method you use to perform a search and replace on your database, there are a few best practices to keep in mind:

1. Always back up your database before making changes. Even if the plugin you‘re using has a backup feature, it‘s a good idea to take a full backup with a dedicated WordPress backup plugin or your hosting provider‘s backup tool. That way you can quickly restore your site if something goes wrong.

2. Double and triple check your search and replace values. When entering text to search for and replace, it‘s easy to make a typo or mistake. Before executing the search/replace, carefully review the "find" and "replace" text to make sure they‘re exactly what you want to update.

3. Run a "dry run" or test the changes first. Most search/replace plugins have an option to preview the changes before actually updating the database. Use this dry run feature to verify the expected text will be updated. If you‘re using a custom SQL query, you can first run a SELECT query using the same search parameters to see what will match, like:

SELECT * FROM wp_posts WHERE post_content LIKE ‘%search_text%‘;

4. Test your site thoroughly after performing a search and replace. Once the database has been updated, review your site to make sure the changes took effect properly and didn‘t break anything. Check a few different pages and post types. If you replaced URLs, click on links to ensure they‘re working.

5. Avoid running search/replace on a live site. If possible, it‘s best to first perform a search and replace on a staging or development copy of your site. That way you can verify the results before pushing the changes live.

Search and Replace Frequently Asked Questions

What happens if I don‘t back up my database before searching and replacing?
If something goes wrong with the search and replace process and you don‘t have a database backup, you may lose data or break your site. Without a backup, it can be very difficult or impossible to undo the changes and restore your site to the previous state.

Can I search and replace text in specific posts or pages?
Yes, most search and replace plugins have options to narrow down the search to specific database tables, like wp_posts for posts and pages. Some plugins, like Better Search Replace, even let you search in a specific post ID. If you only need to update one or a few posts/pages, it‘s best to manually edit those rather than running a database-wide search and replace.

I searched and replaced text but the changes aren‘t showing on my site. What‘s wrong?
First, make sure you cleared your browser cache, as well as any caching plugins on your site, after performing the search and replace. If the changes still aren‘t showing, double check that you searched in the right database table and column. For example, to update post content you need to search in the wp_posts table and post_content column. If you accidentally searched in the wrong place, the text may have been changed but it won‘t be visible on the front end.

What if I need to replace the same text in multiple WordPress installations?
To search and replace across multiple WordPress sites, like in a Multisite network, you‘ll need to use a special plugin capable of this, like Better Search Replace. The other option is to perform the search/replace separately for each site‘s database. Be very careful when making database changes for multiple sites to avoid overwriting the wrong content.

Searching and replacing database text is a powerful way to quickly make site-wide content changes in WordPress. A find and replace plugin is the best solution for most users, as it provides an intuitive interface and dry run features to prevent mistakes. More technical users can also run their own MySQL search/replace queries in phpMyAdmin.

Whichever method you choose, always backup your database first and start with a dry run on a staging site to test the results. With the right precautions, searching and replacing database text can save you hours of tedious work in WordPress!

Similar Posts