New Bookstore! Save 20% on books with discount code: LAUNCH
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!

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!

Jeff Starr
About the Author
Jeff Starr = Creative thinker. Passionate about free and open Web.
WP Themes In Depth: Build and sell awesome WordPress themes.

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. Jeff Starr
    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 &lt; 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. Jeff Starr
    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. Jeff Starr
    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 »
Digging Into WordPress: Take your WordPress skills to the next level.
Thoughts
Take a screenshot with Firefox (no extension required). Open Developer Tools Settings and enable the “Take a screenshot” button. Then click the button :)
Take a screenshot with Chrome (no extension required). Open DevTools, type Cmd + Shift + P, then type screenshot.
After 10 years working on my 2010 iMac, my upgrade finally arrived. Shiny new iMac shipped from Ireland :)
Too much caffeine weirds me out. But I love the taste of coffee. So once in a while I enjoy a small cup of decaf. Hits the spot.
Chris Coyier is a truly awesome person. One of the finest people I've ever worked with. Just #gottasayit
Excel won't open CSV file because SYLK format? Open it with text editor and add an apostrophe ' at the beginning of the file, save changes, done.
Displaying too many social media buttons and links all over the place imho makes you look desperate and frankly kinda sad.
Newsletter
Get news, updates, deals & tips via email.
Email kept private. Easy unsubscribe anytime.