How to string replace on all WordPress posts in MySQL

Sometimes it's useful to know how to bulk edit content in your WordPress MySQL database. Learn how to replace content in all your WordPress posts in one go, using the MySQL REPLACE() string function, and REGEXP_REPLACE() when you need regular expressions.

Bulk edit using MySQL REPLACE() function

MySQL's REPLACE() function is what you use to bulk edit and replace content in WordPress posts through MySQL. The MySQL string function REPLACE() returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str:

REPLACE( str, from_str, to_str )

Replacing strings in MySQL is useful. An example use case: on a WordPress blog there were some bad hrefs in the WordPress content (MySQL table wp_posts). This can be fixed by executing a MySQL UPDATE search & replace on all posts:

UPDATE wp_posts  SET post_content = REPLACE(
  post_content,
  'a class="url" href="www.',
  'a class="url" href="http://www.'
);

After executing this MySQL statement, all occurrences of href="www. in wp_posts are replaced with href="http://www., and thus the hyperlinks are fixed.

MariaDB supports REGEXP_REPLACE since version 10.0.5, and MySQL has it since MySQL 8.0 (see Regular Expressions). REGEXP_REPLACE works like a charm for replacing content by regular expressions:

UPDATE `wp_posts`
SET `post_content` =  REGEXP_REPLACE(
  post_content,
  '<pre class="brush: php;">',
  '<pre>'
);

Since Gutenberg blocks

Since Gutenberg, WordPress saves block metadata into your wp_posts table. A real life example: I wanted to remove shortcode blocks displaying Google AdSense ads. In the MySQL database, the content looks like:

<!-- wp:shortcode -->
[saotn_in_post_shortcode_horizontal]
<!-- /wp:shortcode -->

Yikes, this is multi-line content. I could remove only [saotn_in_post_shortcode_horizontal] and be done with it, but I also wanted to clean up the metadata. Fortunately, this is pretty easily done with MySQL's REPLACE() function. See:

MariaDB [exampleorg]> UPDATE wp_posts
  SET post_content = REPLACE(
    post_content,
    '<!-- wp:shortcode -->\n[saotn_in_post_shortcode_horizontal]\n<!-- /wp:shortcode -->\n',
    ''
  );

Query OK, 38 rows affected (0.114 sec)
Rows matched: 898  Changed: 38  Warnings: 0

As you can see, I replaced the newlines with a \n character, and this worked like a charm. So to recap, if you want to remove a shortcode in your WordPress database, use REPLACE() for a multi-line replace:

UPDATE wp_posts
  SET post_content =
    REPLACE(
      post_content, 'search\nstring\n',
      ''
    );

When replacing syntax highlighting blocks (from "Syntax-highlighting Code Block (with Server-side Rendering)" to "Code Syntax Block", or vice versa) you have to deal with different forms of metadata. For example:

<!-- wp:code {"language":"powershell"} --><pre class="wp-block-code"><code> ... </code></pre><!-- /wp:code -->

versus

<!-- wp:code --><pre class="wp-block-code"><code lang="powershell" class="language-powershell"> ... </code></pre><!-- /wp:code -->

See the difference? You can fix most by using REGEXP_REPLACE():

UPDATE `wp_posts`
SET `post_content` = REGEXP_REPLACE(
  post_content,
  '<!-- wp:code {"language":"(.*)"} -->\n<pre class="wp-block-code"><code>',
  '<!-- wp:code -->\n<pre class="wp-block-code"><code lang="\\1" class="language-\\1">'
);

Here I search for the language name and put it in a back reference \1 for substitution. As long as you know the from and to strings (syntax), you can do most of it directly in the database like this.

More MySQL queries for WordPress: query all WordPress posts in MySQL not having a Yoast SEO meta description, and clean up post revisions.

Replace all instances of a string in WordPress using a plugin

A safer, but less fun 🙂 , way to replace all instances of a string in WordPress is using a plugin. One such plugin is Better Search Replace by Delicious Brains. It also handles serialized data, which a plain REPLACE() query can break when the string length changes.

Prefer the command line? WP-CLI has a wp search-replace command that does the same, including a --dry-run option to see what would change first.

WordPress CMS admin password reset