Spring Sale! Save 30% on all books w/ code: PLANET24
Web Dev + WordPress + Security

MySQL Magic: Find and Replace Data

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!

Want more awesome SQL recipes? Check out Wizard’s SQL Recipes for WordPress 🪄 Features over 300 recipes and an entire chapter on optimizing the WP database! Check out the PDF Demo and get the book »

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!

About the Author
Jeff Starr = Creative thinker. Passionate about free and open Web.
Banhammer: Protect your WordPress site against threats.

11 responses to “MySQL Magic: Find and Replace Data”

  1. Jack Book 2007/07/25 9:40 pm

    Nice catch,
    i may use it someday ;)
    thanks

  2. With this, all instances of http://website have been deleted from the comment_author_url field.

    Hmm. Haven’t all records containing that string actually been deleted, in which case you just killed a bunch of valid data? I think you still want to stick with the UPDATE, setting the field to null where that string occurs.

  3. Perishable 2007/07/29 9:43 am

    Hmm.. yes, good point. I will update the post to reflect this information. Wouldn’t want anyone to lose any valid comment data on my behalf. — Thanks for the catch!

  4. If Identity Insert is enabled on that table, that approach will only work once.

    delete…where id

  5. My < killed the rest.

    Delete from wp_ss_stats where id < n will only delete n records once if id is an identity field.

    Select id from wp_ss_stats limit 0, n

    will return the first n id values.

    Delete from wp_ss_stats where id in (Select id from wp_ss_stats limit 0, n) will delete records in the list of the first n id values where the id range can start at any value.

  6. August Klotz 2007/08/22 11:29 am

    As far as WordPress is concerned, the DELETE command works great for things such as quickly eliminating old records from stats plugins. For example, tables for the popular ShortStat plugins will eventually become oversized and bloated with unused data. Using the DELETE command, it is easy to erase unwanted, ancient data while leaving the current stats unaffected. Simply use:

    DELETE FROM `wp_ss_stats` WHERE id < n

    where n is the number of entries that you would like to delete.

  7. Perishable 2007/08/22 4:58 pm

    Mark,
    Your code returns the following error on MySQL 5.0.27-standard:

    #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

    Looking into it, your code should do the job, but it seems there is a bug in recent versions of MySQL.

    Is there another way to write the query?

  8. I suspect you might be able to use limit on the delete:

    Delete from wp_ss_stats limit 0, n

    but I’d recommend testing it on a copy of a table…

    This confirms that:
    http://dev.mysql.com/doc/refman/5.0/en/delete.html

    And you could do an order by id asc to make sure you’re deleting the “bottom” n records.

  9. August Klotz 2007/08/22 5:18 pm

    Another approach would be to take advantage of the infamous BETWEEN operator:

    DELETE FROM `wp_ss_stats` WHERE id BETWEEN n AND m

    where n, m represent the first record and last record, respectively..

  10. Very true and fine method. However, if your goal was to periodically purge old data, say 100 records every month, you want to be able to do so regardless of what the IDs are.

    What I’m really striving for here is the equivalent of TOP in MSSQL:

    Delete top n * from table

  11. Perishable 2007/08/22 6:04 pm

    I think Mark has the right idea here. A generalized query that is independent of table data (such as id) enables us to automate the procedure at specified intervals via cron, PHP, etc.

    Combining and slightly tweaking the two queries from comment #8 gives us this:

    DELETE FROM `wp_ss_stats` ORDER BY `id` ASC LIMIT n

    ..which seems to work great at deleting the oldest n number of records from the ShortStat table, wp_ss_stats.

Comments are closed for this post. Something to add? Let me know.
Welcome
Perishable Press is operated by Jeff Starr, a professional web developer and book author with two decades of experience. Here you will find posts about web development, WordPress, security, and more »
.htaccess made easy: Improve site performance and security.
Thoughts
I live right next door to the absolute loudest car in town. And the owner loves to drive it.
8G Firewall now out of beta testing, ready for use on production sites.
It's all about that ad revenue baby.
Note to self: encrypting 500 GB of data on my iMac takes around 8 hours.
Getting back into things after a bit of a break. Currently 7° F outside. Chillz.
2024 is going to make 2020 look like a vacation. Prepare accordingly.
First snow of the year :)
Newsletter
Get news, updates, deals & tips via email.
Email kept private. Easy unsubscribe anytime.