Perishable Press

Recently, I needed to find and replace all instances of “http://website” in the wp_comments table of the WordPress database. Fortunately, SQL provides a simple way to find and replace data with its wonderful UPDATE function.

General Example

Using the SQL UPDATE command is straightforward. Here is the general syntax:

UPDATE table_name SET field_name = replace( field_name, 'string_to_find', 'string_to_replace' ) ;

Simply replace the table_name and both instances of field_name with your specific information, and then edit string_to_find, and string_to_replace with the desired values. This is pretty standard stuff, but it is always a good idea to backup your database before executing commands. To run a “Find and Replace” via phpMyAdmin, simply login, select your database, and enter the command via the SQL tab. Copy, paste, and go!

Specific Examples

To use an example from a recent article, let’s find and replace all instances of the string “http://website”. For this example, we want to find all instances located within the wp_comments table of our perishable_wrdp1 database. Specifically, we will query the comment_author_url field of the wp_comments table. Finally, we want to replace all instances of our target string with “http://website.com/”. We use this SQL command:

UPDATE wp_comments SET comment_author_url = replace( comment_author_url, 'http://website', 'http://website.com/' ) ;

..and with that, our database is updated in less than one second. All instances of http://website have been replaced with http://website.com/. Easy, right? Sure, now let’s say we would like to replace all instances of the target string with an empty value, effectively removing it altogether. We would use this command:

UPDATE wp_comments SET comment_author_url = replace( comment_author_url, 'http://website', '' ) ;

Notice the empty set of single quotes ('') indicating the null replacement value. After executing this command, all instances of http://website have been replaced with nothing, which is the same as simply deleting the target string. “Why not just run a simple DELETE command?” I hear you say. Sure, we could do that as well:

DELETE from wp_comments WHERE comment_author_url = "%http://website%" ;

With this, all instances of records associated with (see comment) http://website have been deleted from the comment_author_url field. This command is used frequently in the removal of spam comments from WordPress blogs. WordPress also includes a user-friendly method of mass-deleting or mass-editing comments via its handy “Mass Edit Mode” (WP Admin → Manage tab → Comments tab → Mass Edit Mode link). Simply select any/all comments and click the “Delete Checked Comments” button.

Cheers!