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.
By MySQL REPLACE() function
The MySQL string function
REPLACE() returns the string
str with all occurrences of the string
from_str, replaced by the string
REPLACE() performs a case-sensitive match when searching for
REPLACE( str, from_str, to_str )Code language: SQL (Structured Query Language) (sql)
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.' );Code language: SQL (Structured Query Language) (sql)
After executing this MySQL statement, all occurrences of
wp_posts are replaced with
href="http://www." and thus fixing the hyperlinks.
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>' );Code language: SQL (Structured Query Language) (sql)
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 -->Code language: HTML, XML (xml)
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 easy done with MySQL’s REPLACE function.
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: 0Code language: SQL (Structured Query Language) (sql)
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', '' );Code language: SQL (Structured Query Language) (sql)
Replace all instances of a string in WordPress using a plugin
A safer way to replace all instances of a string in WordPress is using a plugin. One of such plugins is Better Search Replace by Delicious Brains.
Protip: Donate $10, 20 or 30 through Paypal (or see my donate page) and support this site. Thank you <3