Mass string replace in MySQL

I have quite a few times needed to replace strings in my blog’s Db. One simple example is when I changed my blog address and all the URLs to the images were hardcoded. So, I had to go into the db and change the URLs from http://www.nalinmakar.com/blog to http://www.nalinmakar.com. Anyways, so a simple way of doing this is to goto the table that you want to use and open the SQL query interface. Run the following query:

UPDATE tableName SET columnName = REPLACE(columnName, oldString, newString)

so, in my case, I went into the wp_posts table and ran the following query:

UPDATE `wp_posts` SET post_content = REPLACE(post_content, ‚Äúhttp://www.nalinmakar.com/blog‚Äù, ‚Äúhttp://www.nalinmakar.com/‚Äù)

pretty neat‚Ä¶ huh ??

MySQL, Replace, SQL, String

This entry was posted on Tuesday, August 1st, 2006 and is filed under Applications. You can follow any responses to this entry through RSS 2.0. You can leave a response, or trackback from your own site.

#1 by Sweetchillies on August 7th, 2006

it actually is very neat and cool! thanks!

Quote

#2 by kopulex on January 29th, 2009

Hi, this si simple thing. You can read it in official PHP manual.
Do you have any idea’s how can I replace different string at once?

Like:
nick45
nick65
nick36
nick12

To:
nick99
nick99
nick99
nick99

I have in my table many incorrect links, that have been same and i don’t wan’t to use brute-force :] (many many entry’s).

Any idea’s?

Excuse my english..

NULL.in