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
REPLACE() performs a case-sensitive match when searching for
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
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>' );
My name is Jan. I am not a hacker, coder, developer, programmer or guru. I am merely a system administrator, doing my daily thing at Vevida in the Netherlands. With over 15 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, WordPress, websites & optimization. Want to support me and donate? Use this link: https://paypal.me/jreilink.
Restore single MySQL table from a full mysqldump backup file
MySQL InnoDB performance improvement: InnoDB buffer pool instances – Updated!
MySQL database optimization with indices
Convert MySQL MyISAM tables to InnoDB
How to flush MySQL query cache from time to time
Check, repair and optimize MySQL tables with mysqlcheck
How to compare MD5 and SHA1 hashes in MySQL