How to Search and Replace in WordPress database

WordPress Database

A few days ago, an update to the popular WP-CLI utility was released, and one of the most interesting changes is flag support –regexin the command search-replace.

WP-CLI is a system utility for managing WordPress sites from the command line. Many hosting providers have long been installing it for their customers. It allows you to perform a number of useful actions without leaving the command line, including managing users, themes, plugins, updating the core, regenerating thumbnails, and of course searching and replacing the database.

Regular Expressions

The search-and-replace feature in WP-CLI has existed since the tool’s earliest days, but it wasn’t until a recent update (0.20.0) that the developers added a flag –regexthat allows regular expressions to be used:

$ wp search-replace '(foo|bar)' 'baz' --regex

Examples

More recently, we came across one site where the owner also resized medium and large images after changing the theme. At the same time, after the regeneration of all thumbnails, in some records that previously used medium or large image sizes, broken images appeared.

Also see: How to get the number of MailChimp subscribers in WordPress

Solving this problem with a simple search and replacement is difficult, since the path and names of all images are different, and the size is the same only in width. With the help of regular expressions, this task has become much easier:

$ wp search-replace '(wp-content/uploads/.+?)-640x\d+\.(jpe?g|png|gif)' '$1.$3' wp_posts --regex

Thus, all broken images of the form filename-640×300.jpg have changed to their originals.

Another interesting example is to add an attribute target=”_blank”to all links so that they open in a separate tab:

$ wp search-replace '<a([^>]+?)(target="_blank")?([^>]+)>' '<a target="_blank" $1 $3>' wp_posts --regex

Update

If you are already using WP-CLI, then you can update the utility to the latest version using the command wp cli update, or install the new version manually by downloading the distribution kit from the official site.

Be sure to back up the database before doing a search and replace. If you are new to regular expressions, we recommend reading Mastering Regular Expressions

Leave a comment

Your email address will not be published.