Global Find and Replace In Wordpress using MySQL

February 4, 2009 | Barry Wise | Code Samples, Database
29 Comments

Wordpress LogoIn case you ever need to find and replace a text string in every post or page of your Wordpress site, this SQL code will help you out. I recently had to move a client’s website from staging into production, and just when I was getting ready to publish the new site I realized Wordpress’s HTML editor had hard-coded the URLs of every image in every post to the address of the staging server.  This is actually a feature of Wordpress; when you upload an image into a post or page it uses an absolute URL, not a relative one.  This really helps you out if any of your posts get picked up via RSS and published elsewhere, since the images will remain intact thanks to the absolute URLs.

NOTE: An absolute URL is the fully qualified path to a resource; such as http://www.barrywise.com/images/image.jpg.  A relative URL will only work when the file is relative to the path of the current page, such as ../images/image.jpg.

So here I was with a couple hundred pages of content with image URLs all pointing to the wrong address on the staging server.  I needed a quick way to do a find and replace on every post in the Wordpress MySQL database.  It was actually pretty simple.  In my case, I just had to execute this SQL code against the MySQL Wordpress database:

UPDATE wp_posts SET post_content = REPLACE(post_content, 'staging.server.com', 'www.productionserver.com');

This code will search through every “post_content” field in the wp_posts table and replace the staging URL address (staging.server.com) with the production address (www.productionserver.com).  The format is easy to understand and can of course be used with any MySQL table, not just Wordpress:

UPDATE [your_table_name] SET [your_table_field] = REPLACE([your_table_field], '[string_to_find]' , '[string_to_be_replaced]');

I realize this isn’t the most complex or enlightening code sample, but I hope this helps anyone stuck with an annoying repetitive text string in all your Wordpress posts which you’d like to search and replace with one quick command.

Now go out and bombard the interweb with my self-whoring social links:
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • MisterWong
  • StumbleUpon
  • Technorati
  • Diigo
  • FriendFeed
  • Identi.ca
  • LinkedIn
  • PDF
  • Tumblr
  • Twitter
  • Yahoo! Buzz

Tags: code sample, Database, find and replace, mysql, search and replace, wordpress

Similar Posts:

Comments

29 Responses to “Global Find and Replace In Wordpress using MySQL”

  1. Grand Rapids Web Design Grand Rapids Web Design on February 4th, 2009 11:21 am

    I hope I never have to experience that. I forgot that WordPress uses the absolute URL for images. Luckily I’ve never had a huge development site on Wordpress. Thanks for the tip, I’ll but it in my little knowledge vault.

    Casey

  2. Web Design Manchester Web Design Manchester on February 4th, 2009 1:26 pm

    I’m just coming to the end of developing a pretty image-heavy Wordpress site on a test server – I hadn’t even thought about the absolute image URLs!

    But like you say, it’s a fairly simple fix. Thanks for sharing

  3. Bryan Phelps Bryan Phelps on February 5th, 2009 2:09 pm

    Or you can use the WP plugin: http://wordpress.org/extend/plugins/search-and-replace/

    For those of us who tend to only break things on MYSQL…

  4. Barry Wise Barry Wise on February 5th, 2009 2:17 pm

    Thanks, Bryan, I didn’t know there was a plugin for that.

  5. Web Talk Web Talk on February 17th, 2009 12:52 pm

    The only thing i can think of as fr as mysql is concerned is that it is a tricky place to explore! I hope not to get this kind of issue at all. in that case i will call you! you are forewarned! LOL!

  6. Joaquim Homrighausen Joaquim Homrighausen on March 21st, 2009 3:05 pm

    Good post; a handy reference or reminder. One could argue that WordPress should use something like {siteUrl} in its DB content, and then run the various DB columns through a macro parser to make things like this a bit more transparent. This could even be done at the database query level.

    Thanks for the post nevertheless!

  7. Evan Evan on April 5th, 2009 10:46 pm

    I just changed the the location of the images in my blog by unchecking “Organize my uploads into month- and year-based folders” in Settings_Miscellaneous. This simple bit was exactly what I needed to update image urls in my existing posts. It took me A LOT of searching to find it, so maybe by someone else will pick up this comment in the future. Thanks a ton!

    Evan

  8. Jonathan Mark Jonathan Mark on April 11th, 2009 10:48 am

    LOL This is cool. I’m going to bookmark this because I’m sure I’m going to use it someday.I should also check the plugin as well.

  9. Paul Seymour Paul Seymour on April 16th, 2009 11:24 pm

    Great! This will save me heaps of time!

  10. Web Design Studio Web Design Studio on April 18th, 2009 9:26 pm

    Thanks for the tip.

  11. Gavin Gavin on April 27th, 2009 10:17 am

    Thanks for the article, I’ve been looking for a simple way of updating multiple hard coded image URLs for some time and this does the job perfectly.

    The plugin Bryan mentions looks like a good bet for anyone who doesn’t want to get their hands dirty with the SQL though!

  12. Tom Holder Tom Holder on May 6th, 2009 5:01 am

    Thanks, this is great. I’m using onepress community for a blog and for some reason that puts images in as attachments in to the wp_posts table and needs the above doing to the ‘guid’ column!

  13. Gary Gary on June 2nd, 2009 1:45 am

    Thanks Barry! This string is exactly what I have been looking for (am wanting to nest a few UPDATE statements therefore the replace plugins for Wordpress are too simplistic).

  14. Search engine ranking Search engine ranking on June 12th, 2009 12:07 pm

    I think if you keep the same paths and the same name of folders, the moving should not cause any problems. However, if you do need to change something, then this could be a pain, I agree.

    Thanks for sharing your method.

    Tony

  15. deb deb on June 15th, 2009 3:43 pm

    anyone know how to find and replace on “pages”? not just posts…
    thanks!
    deb

  16. Dave Daniels Dave Daniels on June 16th, 2009 7:45 am

    Thank you, this was a HUGE help. I changed my domain name and wanted to change the copyright notice on every post, and this did the trick in seconds instead of HOURS of manually editing each post. THANK YOU!

  17. the website guy the website guy on July 2nd, 2009 1:56 pm

    I’ve had to do this in the past…I just exported the database and did find and replace in text mate… but this sounds a lot easier

  18. heyman Smulders heyman Smulders on July 30th, 2009 11:56 am

    Just wanted to say thanks! I was searching all over for a fix like this. Didn’t even know where to run the script so i added a mini tutorial for dummies like myself below.

    Cheers,
    Heyman

    How to run/execute sql script
    -Login phpMyAdmin
    -Click Databases
    -Click the database Wordpress is using
    -Click the SQL tab
    use the code, hit go

  19. david kovit david kovit on August 7th, 2009 11:11 am

    deb, to answer your question to find and replace on pages just add a where clause for post_type like so:

    UPDATE wp_posts SET post_content = REPLACE(post_content, ’staging.server.com’, ‘www.productionserver.com’)
    WHERE post_type = ‘page’;

  20. ovidiu ovidiu on August 25th, 2009 1:48 pm

    how would I search i.e. mydomain.com and replace with mydomain.org throughout a whole database, not just in posts/pages?

  21. ITCN ITCN on August 28th, 2009 8:45 am

    @ovidiu You can’t do a whole database search and replace at once (at least not with 1 SQL statement); you have to do it table by table, but you can use the same query I noted above.

  22. david kovit david kovit on September 1st, 2009 12:04 pm

    @Ovidiu You could also export your entire database through PHPMyAdmin. Then open the sql file and do a search and replace with a text editor. Once all the instances of mydomain.com have been replaced with mydomain.org you can import the sql file back into the database. You should backup your database before attempting this. It will do what you need though.

  23. Auctions Auctions on September 3rd, 2009 12:39 am

    Thank you, this was a HUGE help and this will save me heaps of time!

  24. irms irms on September 4th, 2009 5:33 pm

    @Ovidiu I wrote a quick script you can upload to your server and do a find and replace across an entire database. Use at your own risk! There are no real safeguards in this script. One string simply replaces another string.

    http://blog.irmsgeekwork.com/2009/phpmysql-full-database-search-and-replace

  25. ovidiu ovidiu on September 9th, 2009 4:05 am

    thx, will check it out

  26. Neil Neil on December 25th, 2009 11:25 pm

    I love the internet, cause smart people like you can make clever people like me – POWERFUL!!

    cheers to having the answer when I needed it!!

  27. ADA sport ADA sport on December 31st, 2009 2:34 pm

    This string is exactly what I have been looking Thanks …

  28. Jaco Costa Rica Condos Jaco Costa Rica Condos on January 21st, 2010 6:21 pm

    @David, that is a good idea for the global search and replace. My question is, does the mysql file keeps its structure?

    for example, I download and open up in word. Do the search and replace and save. Upload to the server? Is that correct? The file structure should remain the same??

    Thanks in advance

  29. Ramon Fincken Ramon Fincken on January 30th, 2010 5:48 am

    I’ve written a plugin at WordPress.org which enables you (with a GUI) to find and replace in your posts,pages and/or trashed items: http://wordpress.org/extend/plugins/find-replace/

Leave a Reply