How to string replace on all WordPress posts in MySQL

Learn how to replace content in your MySQL database in bulk with MySQL REPLACE. Sometimes it’s useful to know how to bulk edit content in your WordPress MySQL database, using MySQL’s REPLACE() function. Here is how to string replace content in WordPress wp_posts table to bulk edit 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, for example an use-case: on a WordPress blog there were some bad href’s 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 fixing the hyperlinks.

MariaDB supports REGEXP_REPLACE. It was introduced in version 10.0.5: https://mariadb.com/kb/en/mariadb/regexp_replace/. REGEXP_REPLACE seems to work like a charm for replacing content by regular expressions:

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

Did this post helped you solve a problem? Or dit you find it interesting? Support Sysadmins of the North with a direct donation via Paypal of by bank wire transfer IBAN: NL31 ABNA 0432217258 (Jan Reilink). Just $ 5,- or € 5,- is more than enough, thanks!

Support SAOTN.ORG


681 views

Leave a Reply

Your email address will not be published. Required fields are marked *