Disclaimer: You are looking at a post I wrote some time ago. The information and opinions contained within may be outdated and may differ from my current views. Please proceed accordingly.

Find-and-Replace in MySQL

Sep 25, 2003 3:02 PM
Tags:
Apparently you can write one-line find-and-replace statements in SQL. I didn't realize you could do this so easily in MySQL; I would have resorted to a script.

update TABLE set COLUMN = replace(COLUMN, OLD_VALUE, NEW_VALUE);


Picked this up at the mysql community on LiveJournal, one of the places there that is useful and not just entertaining.

If you need something more heavyweight, recent versions of MySQL have a regexp function.

Comments: Find-and-Replace in MySQL

It would be good to clarify whether this works on substrings also. Sorry if this is obvious, but I am getting ready to try this SQL on our database, and I'm hesitant to press enter :) I will verify the answer before I do.

Posted by: Patent Searcher on January 14, 2007 6:50 PM | permalink

Umm ... you shouldn't need me for that.

Just make a test table, and try it out for yourself.

That's a lot quicker (and more polite) than asking someone else to research something for free.

Posted by: Joe Grossberg on January 14, 2007 10:50 PM | permalink

Thanks a lot, I was just searching for this!

Posted by: muztagh on March 10, 2007 1:30 AM | permalink

Just what I was searching for, thanks.

Posted by: Mike on April 2, 2007 2:59 AM | permalink

This is great - fixed a problem instantly.

Posted by: Marc P Summers on April 16, 2007 5:43 AM | permalink

Thanks for the info. Before I found this I used to export all my data into Excel!!

Posted by: pete on April 23, 2007 5:11 AM | permalink

You saved me some bucks. A programmer told me it was complicated to change the comma (,) in a field to a period (.) for 1300 records. Thanks so much.

Posted by: Ellen Chen on July 31, 2007 9:20 AM | permalink

You mention the use of regular expressions, but they can only be used to select data, because the REGEXP operator does not capture in MySQL.

There is apparently no way to do a preg_replace without external scripts...

Posted by: mauhiz on August 22, 2007 9:29 AM | permalink

Thanks a lot...this was exactly what i was looking for...

Posted by: on December 31, 2007 4:51 AM | permalink

No more comments! Either someone has violated Godwin's Law, I'm tired of the discussion or, most likely, the ten-week window has closed. You can, however, contact me through email.