Global Find and Replace In Wordpress using MySQL
February 4, 2009 | Barry Wise | Code Samples, Database
29 Comments
In 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:
- ITCN ASP.NET Discussion Forum Software Version 3.1 Released
- How to Create 404 Page Not Found Custom Error Pages in PHP
- Google Releases Official SEO Starter Guide
- SEO Canonical URLs And 301 Redirects In Windows IIS 6, IIS 7
- ITCN Releases Youtuber2 Plugin for Wordpress
Comments
29 Responses to “Global Find and Replace In Wordpress using MySQL”
Leave a Reply






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
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
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…
Thanks, Bryan, I didn’t know there was a plugin for that.
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!
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!
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
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.
Great! This will save me heaps of time!
Thanks for the tip.
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!
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!
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).
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
anyone know how to find and replace on “pages”? not just posts…
thanks!
deb
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!
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
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
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’;
how would I search i.e. mydomain.com and replace with mydomain.org throughout a whole database, not just in posts/pages?
@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.
@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.
Thank you, this was a HUGE help and this will save me heaps of time!
@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
thx, will check it out
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!!
This string is exactly what I have been looking Thanks …
@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
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/