MySQL: Find and Replace

MySQL Search and ReplaceYou have a set of data and you want to update all value of one string to another. While this in concept is easy enough, you may not know the proper command syntax off the top of your head. Here’s the solution:

Search and Replace SQL Query

Typically you know where the data is being stored when you’re running a query of this nature. Here’s the bit of code I like to use to run my search and replace queries when working in MySQL databases:

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

Of course, you will need to replace my generic query place holder text with actual data. Let’s see some useful search and replace queries for WordPress.

Useful WordPress Search and Replace Queries

Find-and-replace a string in your WordPress wp_posts table:

UPDATE wp_posts SET post_content = replace( post_content, 'string_to_find', 'string_to_replace' ) ;

Find and Replace an IP in the wp_posts’ guid column

UPDATE wp_posts SET guid = REPLACE( guid, '64.837.1.172', 'www.mywebsiteurl.com' ) 

Find and Replace an IP in the wp_postmeta‘s meta_value column

Find and Replace an IP in the wp_posts’s post_excerpt column

UPDATE wp_posts SET post_excerpt = REPLACE( post_excerpt, '64.837.1.172', 'www.mywebsiteurl.com' ) 
UPDATE wp_postmeta SET meta_value = REPLACE( meta_value, '64.837.1.172', 'www.mywebsiteurl.com' ) 

I would not recommend doing a find-and-replace in the wp_options table since it can lead to corrupting multiple widgets and plugins. Just search on this table within PHPmyAdmin for the string you’d like to replace and do it by hand is what works for most cases.

Please ensure you have backed up your MySQL Database before executing any query!

Where to Enter Your Query

Again, back it up then… Once you login to PHPmyAdmin click on a table in the left navigation pane and then on the Query tab. There you can run your various queries in that table. Here’s a screenshot:

MySQL Query Tab

Search and Replace Entire DB

As far as I know there’s no one query that will search and replace all tables for you. Although, If you are working with WordPress there’s a really good plugin I like to use called Search and Replace. It’s good a running multiple queries and allows your to really define your search in a GUI. Check out some screenshots:

Search and Replace WordPress Plugin

Search and Replace Screenshot

Leave a Reply

Your email address will not be published. Required fields are marked *