Free AntiVirus!
Mass Replace String in MySQL Database
Filed in: Web Development, WordPress — April 24th, 2005Since I have changed the blog script path from my previous installation. I need to update the files and images path URL in all previous posts.
Do it manually? No way. There are hundreds of them!
There are basically two ways suggested in my search. First, output the database table and use text editor's "search and replace" function. Second, use MySQL REPLACE function.
Of course, the later is better and easier solution. Here is the SQL query to replace string in database table:
SQL:
-
UPDATE table_name SET column_name = REPLACE(column_name,"original_string","replace_string")
Here is what I did to change the path URLs in all the previous posts.
SQL:
-
UPDATE `wp_posts` SET `post_content` = REPLACE(`post_content`,"liewcf.com/wp","liewcf.com/blog")
300+ records updated. Time spend: 1 minute. 
Popularity: 3% [?]
Comments Feed
TrackBack URL
April 24th, 2005 at 11:08 pm
Aha.. I see.. This is rather useful. Overall, cool URIs rule!
April 25th, 2005 at 12:39 pm
Hi,
Is there a way to close commenting for old posts in WP? I am getting hit hard by comment-spam and would like to know if there is way to shut off commenting for posts that are older then 3 months…
April 25th, 2005 at 6:10 pm
@caleb: here you are Auto_shutoff_comments
October 12th, 2005 at 8:31 am
[...] Today I was searching for WordPress plugins (none particular) and I found this site. This guy wrote an interesting section dedicated to WP tricks. One post is very useful: hot to Mass Replace String in MySQL Database. [...]
December 10th, 2005 at 11:07 pm
[...] Qualche volta può capitare di dover sostituire delle stringhe (testi, cifre etc) nel nostro database mySQL. Se il database contiene centinaia di records (come spesso capita) è impensabile effettuare le sostituzioni manualmente. Possiamo però usare un semplice trucchetto, illustrato qui. [...]
March 26th, 2006 at 7:45 am
Hi guys! This Trick is very cool, but if i want to use same code for searching and replacing string in entire databse (not only Specified TABLE and Column). Whic syntax can i write? Can you help me?
see you, alessandro
April 15th, 2006 at 5:06 am
[...] Then, using phpMYAdmin, I updated the strings <itag> and </itag> to respectivly for all the posts. The former custom HTML tags are used by WP Taggerati to define tags, where as the latter are used by UTW. NOTE: I could have modified UTW’s PHP code too, but I didn’t want to use the ‘<itag>’ format. I searched a bit and found the information on how to replace the strings on LiewCF.com. [...]
July 25th, 2006 at 6:33 am
[...] Qualche volta può capitare di dover sostituire delle stringhe (testi, cifre etc) nel nostro database mySQL. Se il database contiene centinaia di records (come spesso capita) è impensabile effettuare le sostituzioni manualmente. Possiamo però usare un semplice trucchetto, illustrato qui. [...]
March 10th, 2007 at 9:07 am
Great tip for those of us not too familiar with simple mysql queries.
April 10th, 2007 at 10:00 am
You are a legend I was just wondering how to do a batch replace on a SQL DB. I was considering doing an export, and using TextPad (notepad would never hand the file_size!) but your simple command did the trick.
August 24th, 2007 at 5:16 am
[...] I show you how to update all the old TLA affiliate links in one shot (WordPress only) using Mass String Replace in MySQL: PLAIN TEXT [...]
December 10th, 2007 at 8:09 pm
hi.
thanks for the tip. however, you might want to mention that people do a BACKUP first.
because brain-dead me completely f*cked up a client’s database by replacing the content with a link.
i hope the hoster has a backup.
yes, i know, i’m at fault. just want people to know that there is no UNDO in phpmyadmin
December 11th, 2007 at 10:47 pm
small update: was able to get a restore from my provider for 80 bucks excl. tax.
then i made my own backup and tried the replace string again
worked!
February 9th, 2008 at 11:55 am
WOW! This was great - the SQL was all it took to move my site from one domain to another without any additional issues. And to think, I was going to spend hours doing this manually.
Thanks!!!
Mohamed
February 12th, 2008 at 9:41 pm
Thanks a lot for the tip.
It saved me half a day of manual repetitive editing.
Cheers
April 9th, 2008 at 2:47 am
[...] to Liewcf, it was really wuick to fix, even in the tech feed I also [...]
June 10th, 2008 at 12:11 am
Shame there is not a utility to replace strings in the entire table or database…. if you have lots of tables / column names to check it gets messy having to iterated them all, but I have not come across another way of doing it…