When there are hundreds or thousands of posts and comments on a blog it becomes more difficult to manage because you might need to make changes to several posts at once. For example, you might transfer images to a dedicated CDN server to serve images quicker. This means all the links in your posts showing images will need replacing with the new URL. Or you might have tried loads of plugins and redundant data has been left behind in the posts as a side effect.
By default WordPress cannot bulk edit the content of multiple posts at once so another way is needed to do it. Here we have 5 different methods for you to try out.
1. Search and Replace WordPress Plugin
There are several WordPress plugins that enable you to perform a search and replace on text within most areas of your blog. The Search & Replace plugin has been chosen here because it’s got enough features for general use, is popular, easy to use and receives periodic updates.
2. In the admin sidebar go to Tools > Search & Replace. Enter the search string and replace string (can be left blank for deleting) then select only the wp_posts table in the list.
3. Click the Do Search & Replace button. As the Dry Run box is ticked by default it will only search and show what the changes will look like. At the top you will get a result box saying what was found and what would be changed in a real replace. Click “View details” to see a before and after window.
4. When you’re happy with everything untick the Dry Run box and select to save changes to the database and press the Do button. If you have no prior backups exporting to SQL file with changes first would be a safer option.
The good thing about Search & Replace is it gives the ability to backup and import the SQL database as well as helping with migrating from one domain to another.
Note: If the search function of Search & Replace isn’t quite powerful enough, another plugin we have talked about before called Search Regex might be worth a try. It includes powerful regex search options to finely tune your search terms. Sadly it hasn’t been updated for a while and other functions like searching titles, excerpts and post URLs no longer work, post content searching and replacing does still work though. Make sure you have database backups before trying it.
2. Database Search and Replace PHP Script
This works by using a PHP script that you upload to your server and then make search and replace modifications to the SQL database. It’s more user friendly than going through phpMyAdmin while still offering enough power for most users.
1. Download Search Replace DB and extract the Zip file. Enter the Search-Replace-DB-master folder to find another with the same name. Optionally rename it for security purposes.
2. Upload that folder to your server and place in the same location as wp-admin, wp-content etc. In your browser navigate to the folder, e.g. mysite.com/blog/Search-Replace-DB-master.
3. Once loaded check the script has automatically found the WordPress database. If not you will need to manually supply the details. The search and replace boxes are self explanatory with the additional option of Regex for advanced users.
4. In Tables choose “select tables“, scroll down the list, find the wp_posts table and highlight it.
5. Now you are ready to test or apply the changes. Click Dry Run to perform a search to see how many occurrences are found and what will be changed if the replace is executed for real. When you are happy click Live Run to commit the changes to the database.
Once you’re finished with Search Replace DB click the Delete button to remove it and secure the server.
3. Simple Find and Replace With phpMyAdmin
People with access to the WordPress SQL database can edit multiple posts directly from phpMyAdmin quite easily. This can be risky if you are not familiar with phpMyAdmin so less experienced users should consider this as a last resort for use only if you’re having issues with easier methods. As always when editing the database, adequate backups are recommended.
1. Connect to phpMyAdmin through your host, such as cPanel etc. Click on the WordPress blog database in the database list.
2. In the table list find and click on the wp_posts table and then the Search button at the top in the main window. Press the Find and Replace button just below.
3. The Find and Replace window is pretty straightforward, enter the string to search for and the string to replace it with, a regular expression option is available for more advanced usage. The drop down menu defaults to searching post content but can be changed to post titles or post excerpts etc. Clicking Go will begin the search through the posts in the database.
4. If anything has been found a preview of the affected articles will show with the original text on the left and what the changes will look like on the right. Click Replace if you are happy with the changes.
You cannot delete text using this method and must supply a replace with string, a solution to that is the more advanced manual search and replace below.
4. Manual Find and Replace With phpMyAdmin
The simple find and replace above is merely a frontend for manual SQL queries but you can enter these commands manually if you wish although discretion is advised as this method is potentially a bit more dangerous.
1. Connect to phpMyAdmin and select the WordPress database in the list. Click SQL at the top in the main window.
2. The SQL query window allows you to enter all kinds of different queries, a search and replace on blog post content is relatively simple:
UPDATE wp_posts SET post_content = REPLACE (post_content, ‘search for’, ‘replace with’);
The same query has been cleaned up below and made easier to read using the Format button.
3. Once you are sure your query is correct press the Go button. Note this does not offer a preview like the simpler find and replace and the action is immediately applied to the database.
An advantage with this method is you can use it to remove text, enter a blank string for replace with:
UPDATE wp_posts SET post_content = REPLACE (post_content, ‘deletethis’, ”);
Although we won’t go into it here you can use more advanced queries to find what you are looking for, such as the LIKE command to search for similar terms and replace other post content such as titles or excerpts. The MySQL manual has lots more information on the subject.
5. SQL Queries From a WordPress Plugin
If you want to edit the database post content using the the same SQL queries as above but don’t have access to phpMyAdmin, there is a WordPress plugin available called SQL Executioner that allows you to do this.
1. Download SQL Executioner, extract and upload to the plugins folder on your server or install it directly from WordPress admin (Plugins > Add New > search for SQL Executioner > Install Now > Activate).
2. In the admin sidebar go to Tools > SQL Executioner, enter your chosen SQL query into the textbox and press Execute SQL to run the query.
The command is executed immediately and is irreversible, so make sure you type it in correctly and have adequate backups just in case.
Tip: If your posts table does not have the standard name of wp_posts you can find what it’s called by clicking on $posts and looking at Raw query just below Results.