Bulk replace text in all of your WordPress posts using SQL

Do you have some text or HTML across your site that you’d like to change? For example, maybe you want to change every instance that you use the <h3> tag, and instead use the <h4> tag.

You can make changes like this by writing and running some SQL.

Changes like this can be risky if you don’t understand what you’re doing. Ensure that you backup your database, and understand how to restore it (if the need arises) BEFORE attempting any of what you see below. If you break your site and don’t have a backup, that’s on you.

Once again, MAKE A BACKUP OF YOUR DATABASE BEFORE ATTEMPTING THIS.

Ok, now to the fun part:

First, connect to your site’s MySQL database using a remote access tool. In my case I used MySQL Workbench.

Next you’ll want to run an UPDATE SQL statement to make the changes. Observe the statement below:

UPDATE 
databasename.wp_posts
SET
post_content = REPLACE(post_content, '<h3>','<h4>')
WHERE
post_type = 'post'
AND post_status = 'publish'

If you’re familiar with SQL this should make a lot of sense. We’re running an UPDATE on the table databasename.wp_posts. You’ll want to change databasename to the name of your database. For many hosts your database name will be a randomly generated string and you may have used it to connect the the MySQL database in the first place. (Admittedly, you don’t need to explicitly name the database when writing SQL, but it removes potential ambiguity in the command and makes me feel better).

We’re SETting each row’s post_content cell to the value of the current post_content cell (a string), but with any instances of the ‘<h3>’ substring replaced with the ‘<h4>’ substring. Read more about REPLACE and you’ll see that’s how this function works. Effectively, this is like running a find and replace in Notepad and replacing instances of  ‘<h3>’ with ‘<h4>’.

The WHERE clause ensures that we only update published posts (not pages, attachments, etc). If you wish to apply this REPLACE to a different set of data, you’ll need to learn about the possible values of columns in the wp_posts table, and adjust your SQL accordingly.

Don’t forget the end tags! Unintended things may happen if you don’t update both the opening and closing header tags.

UPDATE 
databasename.wp_posts
SET
post_content = REPLACE(post_content, '</h3>','</h4>')
WHERE
post_type = 'post'
AND post_status = 'publish'

That’s it! Run your UPDATE statements, and the text you wish to replace should be updated. If something went awry, you can use your backup to recover your site. Happy updating!

Leave a Comment

Your email address will not be published.