Remove/Replace Content from the WordPress Database

A useful tool to have in in your WordPress toolbelt is the ability to quickly and easily search for, find, and replace specific strings of text directly from the MySQL database. We can do this by entering SQL queries either directly or through one of those handy interface applications like phpMyAdmin, which seems like one of the most prevalent PHP applications on servers today.

Hopefully, you have some experience working directly with the database, but even if you’re new to it, the simple recipes presented in this DiW article will help you find, replace, and delete specific text content using a few simple SQL commands. This gives you incredible power to make sitewide changes to your posts, comments, or any other database table with a few simple clicks.

Before we get started, remember to

backup your database before making any changes!

Once we’ve got our database backup(s), we’re ready to dig into some easy, effective SQL queries for manipulating post content. To give us some context while we go, we’ll assume a hypothetical case: let’s say we want to find and remove all instances of the mystical nofollow attribute, in all of its various incarnations:

• rel="nofollow"
• rel="external nofollow"
• rel="nofollow external"

In addition to these instances of the attribute, our custom query will also match any instances of other attribute values besides “external”. A couple of things to keep in mind about SQL selectors:

%   => this is the "wildcard" selector - will match /any/ character
*   => this is the "all" selector - will match /all/ entries

All right, let’s look at some of the useful things we can do using a few custom SQL queries..

Display all instances of a given text string

Before making any sitewide changes to your database, it’s a good idea to simply search and find all instances of a given text string. So in our example, we are looking for three different nofollow patterns, as described above.

Using phpMyAdmin, click on the “SQL” tab and enter the following SQL query:

SELECT * FROM wp_posts WHERE ( 
	post_content LIKE '% rel="nofollow"%'
	OR post_content LIKE '% nofollow%'
	OR post_content LIKE '%nofollow %'
);

This query selects everything from the wp_posts table that matches any of our three target cases. Note that if you are using a unique table prefix (i.e., anything other than “wp_”), you will need to change the “wp_posts” in the first line to match.

From here, you can check the results by evaluating the overall number of posts and “zooming in” on any specific post(s) of interest. To search for a text string in a different table, replace “wp_posts” with something else, such as “wp_comments” or whatever you like.

If desired, we could locate all instances of each specific pattern individually using each of the following queries:

SELECT * FROM wp_posts WHERE (post_content LIKE '%nofollow %');
SELECT * FROM wp_posts WHERE (post_content LIKE '% nofollow%');
SELECT * FROM wp_posts WHERE (post_content LIKE '% rel="nofollow"%');

So with our nofollow example, let’s say we have 100 posts that contain some instance of the target string. We no longer want these attributes polluting our post content, so let’s remove them..

Remove all instances of a given text string

Once we are ready to actually remove all instances of the target string in our post content, we run the following three queries, either all at once or one at a time:

UPDATE wp_posts SET post_content = REPLACE ( post_content, 'nofollow ', '' );
UPDATE wp_posts SET post_content = REPLACE ( post_content, ' nofollow', '' );
UPDATE wp_posts SET post_content = REPLACE ( post_content, ' rel="nofollow"', '' );

Bam! Just like that, we have removed all of those silly nofollow attributes throughout our entire site without affecting anything else. It’s as if they never existed in the first place. So much power! ;)

How does it work? Easy: each command is saying, “replace all matches in the post_content field with exactly nothing.” As far as I know, there is no way to combine these queries into a single command, so if there are any SQL wizards reading, please enlighten.

As you may guess, the general pattern for removing any text string from all post content in the database looks like this:

UPDATE wp_posts SET post_content = REPLACE ( post_content, 'text to be replaced', '' );

And from here, we can just as easily replace any matching text..

Replace all instances of a given text string

Lastly, let’s look at how to replace our text string with some different text. We’re actually using the same query as in the previous section, only instead of empty quotes in the second argument, we’re going to add our replacement text.

Let’s say that, instead of deleting our nofollow attributes, we want to replace them with “dofollow”. We wouldn’t actually do this on a production site because dofollow is not a valid value for the rel attribute. But for the sake of our hypothetical, let’s just do it anyway. Here’s how it looks:

UPDATE wp_posts SET post_content = REPLACE ( post_content, 'nofollow ', 'dofollow ' );
UPDATE wp_posts SET post_content = REPLACE ( post_content, ' nofollow', ' dofollow' );
UPDATE wp_posts SET post_content = REPLACE ( post_content, ' rel="nofollow', ' rel="dofollow' );

Same idea as before, only now we can see that anything can be used for the replacement text, even imaginary attribute values ;)

And finally, here is the general formula for replacing post content via SQL:

UPDATE wp_posts SET post_content = REPLACE ( post_content, 'text to be replaced', 'replacement text' );

And of course keep in mind that this query may be modified as explained in the article to search for, remove, and replace any content – markup, text, special characters – in your WordPress database. For site admins and serious bloggers, this is an excellent way to speed up maintenance and get things done.

Of course, there’s probably a plugin that will do all of this for you ;)