Global Find and Replace In WordPress using MySQL

February 4, 2009 | Barry Wise | Code Samples, Database
28 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.

Bookmark and Share. Share, I said!
  • 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

28 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. Web Design Studio Web Design Studio on April 18th, 2009 9:26 pm

    Thanks for the tip.

  9. 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!

  10. 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!

  11. 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

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

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

  13. 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!

  14. 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

  15. 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

  16. 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?

  17. 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.

  18. 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

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

    thx, will check it out

  20. 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!!

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

    This string is exactly what I have been looking Thanks …

  22. 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

  23. 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/

  24. Steve Polacek Steve Polacek on August 22nd, 2010 4:02 pm

    Thanks Barry! I just used this and it worked like a charm!

  25. Mark Fregnan Mark Fregnan on January 2nd, 2011 9:46 pm

    Thank you very much Barry.

    Your post saved me hours of work. We wanted to change the HTML code paragraph formatting on our posts and pages and your SQL query did the trick.

    I also used the WHERE statement…

    -Logged into phpMyAdmin via my Host Cpanel
    -Selected the WordPress MYSQL database from the list of databases
    -Clicked on table wp_posts
    -Clicked the SQL tab
    -Cut and pasted the code below, and clicked [Go]

    UPDATE wp_posts SET post_content = REPLACE(post_content, ‘OLD HTML’, ‘NEW HTML’) WHERE post_type = ‘post’;

  26. Jim Jim on March 8th, 2011 12:50 pm

    You probably want to also do the search and replace on the wp_postmeta table.

  27. Shovan Shovan on March 25th, 2011 7:01 am

    Yay! It works, But my WP-Admin still redirects to old site.. any ideas?

  28. Leland Gold Leland Gold on June 7th, 2011 9:55 am

    Thanks a lot barry man! I use this so much now, I’m not sure what I would do without it. Just got into this myself, but thanks again for the help!

Leave a Reply