Global Find and Replace In WordPress using MySQL

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.

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

Similar Posts:

Comments

36 Responses to “Global Find and Replace In WordPress using MySQL”

  1. 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 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 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 on February 5th, 2009 2:17 pm

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

  5. 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 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 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 on April 18th, 2009 9:26 pm

    Thanks for the tip.

  9. 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 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 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 on June 15th, 2009 3:43 pm

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

  13. 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 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 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 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 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 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 on September 9th, 2009 4:05 am

    thx, will check it out

  20. 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 on December 31st, 2009 2:34 pm

    This string is exactly what I have been looking Thanks …

  22. 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 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 on August 22nd, 2010 4:02 pm

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

  25. 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 on March 8th, 2011 12:50 pm

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

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

  29. Mary on November 22nd, 2011 10:39 pm

    Awesome! I just started learning MySQL so this was magical. I just migrated a client site from godaddy’s “preview dns” to the live site – without realizing the images wouldn’t migrate. Saved me tons of time and work, and taught me something new – thanks!

  30. Don on March 24th, 2012 5:45 pm

    Hello, what I’m trying to do is globally replace all urls to a specific domain which is the the content of many posts but since each url is different only the domain name remains the same I’m having a problem. I figured smthg like:

    UPDATE wp_posts SET post_content = REPLACE(post_content, ‘www.domain.com/%’, ‘www.new.com’);

    But how do I limit it to just the url and not replace everything including content after the url?

  31. Tom on March 27th, 2012 11:11 am

    It’s also likely necessary to run this:

    UPDATE wp_posts SET guid = REPLACE(guid, ‘old.com’, ‘new.com’);

    to update any image attachment URLs.

  32. Chris Pink on June 20th, 2012 7:48 am

    It’s almost right (not sure why nobody has picked up on this, maybe it’s down to a phpmyadmin version change).

    you need to enclose ‘post_content’ in single quotes thus;

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

  33. Barry Wise on July 2nd, 2012 2:10 am

    Hi Chris;
    I think that’s just your phpmyadmin. I use it without single quotes without a problem

  34. Steve on July 20th, 2012 4:15 am

    Ive got a huge task of updating 4000 pages that contain a nextgen gallery, and wandered if anyone could help.

    I actually need to change the HTML of every page that uses a specific page template

    I need to look for the following, however I need to be able to put in a global character search as every page uses a different Nextgen Gallery ID Number.

    [nggallery id=XXXX]

    And replace it with
    [one_half]
    [nggallery id=XXXX]
    [/one_half] [one_half_last]
    Release Details

    Record No
    Artist
    Title
    Album Single
    Format
    Catalogue Number
    Record Label
    Release Date
    Country
    Promotional Item
    Notes

    Tracks:
     
     
    Song Credits:

    Production Credits:
     

    Other Credits:
     
    [/one_half_last]
     
    [one_half]
     

    Can anyone suggest whether this is possible in anyway, it could save me a significan amount of time!!

    Many thanks

    Steve

  35. rozina on July 26th, 2013 2:51 pm

    Never enclose ‘post_content’ in single quotes. You will lose all your posts – The command will wipe out all your post content and replace with just the new url name.

  36. Website Development on September 3rd, 2013 3:35 am

    This code really works and we find and replace the code using Update SQL string.

    Thanks