Mass Replace String in MySQL Database

Filed in: Web Development, WordPress — April 24th, 2005

Since 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”)[/sql]

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”)[/sql]

300+ records updated. Time spend: 1 minute. :grin:

technorati tags , , , , , ,

Like this post? Please share:

Follow @liewcf on Twitter; Join Facebook page; Subscribe to free newsletter for updates like this article..

  • http://cheeaun.phoenity.com/weblog/ Lim Chee Aun

    Aha.. I see.. This is rather useful. Overall, cool URIs rule!

  • http://cheeaun.phoenity.com/weblog/ Lim Chee Aun

    Aha.. I see.. This is rather useful. Overall, cool URIs rule!

  • http://cowboycaleb.liquidblade.com/ caleb

    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…

  • http://cowboycaleb.liquidblade.com caleb

    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…

  • LcF

    @caleb: here you are Auto_shutoff_comments

  • LcF

    @caleb: here you are Auto_shutoff_comments

  • Pingback: Mr.Brown’s Blog » A good site for WP Tricks

  • Pingback: SocInf » Archivio Blog » mySQL. Sostituzione automatica di stringhe.

  • Alessandro

    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

  • Alessandro

    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

  • Pingback: NalinMakar.com : Blog Archive : Installed UTW3 for Tags

  • Pingback: mySQL. Sostituzione di massa di stringhe » Studio404

  • http://theprogressbar.com/ David Evans

    Great tip for those of us not too familiar with simple mysql queries.

  • http://theprogressbar.com/ David Evans

    Great tip for those of us not too familiar with simple mysql queries.

  • http://www.ambrand.com/ Ambrand Dot Com

    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.

  • http://www.ambrand.com Ambrand Dot Com

    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.

  • Pingback: How to Update Text Link Ads Affiliate TinyURL in One Shot

  • http://www.mores.cc/ mores

    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 :(

  • http://www.mores.cc mores

    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 :(

  • http://www.mores.cc/ mores

    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 :D
    worked!

  • http://www.mores.cc mores

    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 :D
    worked!

  • http://www.oibo.org/ Mohamed Bhimji

    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

  • http://www.oibo.org Mohamed Bhimji

    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

  • http://www.iaminchina.com/ Chris

    Thanks a lot for the tip.
    It saved me half a day of manual repetitive editing.

    Cheers

  • http://www.iaminchina.com Chris

    Thanks a lot for the tip.
    It saved me half a day of manual repetitive editing.

    Cheers

  • Pingback: Allan at Work » Blog Archive » Change of Name

  • http://www.sitelogic.co.uk/ Jez

    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…

  • http://www.sitelogic.co.uk Jez

    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…

  • http://www.seo-analysis.com/ Serge

    brilliant … just what i needed :-)

  • http://www.seo-analysis.com Serge

    brilliant … just what i needed :-)

  • Juan

    just wondering whether this possible

    I would like to get rid off of a word in a string. Below is an example.

    “Here you can learn”

    Now, what I would like to do is to take of the word “can”.

    How do I do that ?

    thanks

  • Juan

    just wondering whether this possible

    I would like to get rid off of a word in a string. Below is an example.

    “Here you can learn”

    Now, what I would like to do is to take of the word “can”.

    How do I do that ?

    thanks

  • http://www.mores.cc/ mores

    REPLACE(`post_content`,”can”,”")

    You might want to remove a leading or trailing space as well

  • http://www.mores.cc mores

    REPLACE(`post_content`,”can”,”")

    You might want to remove a leading or trailing space as well

  • http://justkhai.com/ khai

    i’m follow what u wrote.. but i got this error… anyone?

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘”justkhai.com”,”blog.justkhai.com”)’ at line 1

    my old url: justkhai.com
    now: justkhai.com/blog

    anyone pls give me a favor :D tq

  • http://justkhai.com khai

    i’m follow what u wrote.. but i got this error… anyone?

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘\”justkhai.com\”,\”blog.justkhai.com\”)’ at line 1

    my old url: justkhai.com
    now: justkhai.com/blog

    anyone pls give me a favor :D tq

  • http://justkhai.com/ khai

    to anyboady had problem like me, just chmod your htaccess to 777… and everything ok… thanks

  • http://justkhai.com khai

    to anyboady had problem like me, just chmod your htaccess to 777… and everything ok… thanks

  • lec

    Thank you so much dude! this helped me a lot. :)

  • lec

    Thank you so much dude! this helped me a lot. :)

  • Chris

    Thanks mate, this one greatly helped me out..

  • Chris

    Thanks mate, this one greatly helped me out..

  • penyatd

    thanks!!

t