MySQL search and replace

16 Apr 2004

How to perform a mySQL search and replace.

update tablename set field = replace(field,'search_for_this','replace_with_this');

If only I had found that earlier :-P

Posted by François Nonnenmacher on Friday, April 16, 2004 at 3:49 PM

Filed in

14 Comments

Wow, weird syntax.

garoo | April 16, 2004 10:06 PM

You just saved my night :)

ap | April 27, 2004 11:48 PM

Thanks. Works great...

z33k | June 18, 2004 5:09 AM

i've been searchin for this, thanks a lot ;)

h8R | December 11, 2005 4:20 PM

And even after all this time, it's still helping people out. Saved me a ton of time. Thank you.

Luke | August 29, 2006 6:04 PM

Thanks! This helped me a lot.

I put it in camel casing so that it was more obvious for myself, and hopefully for others who find this:

UPDATE tablename SET fieldname = REPLACE(fieldname, 'badterm','goodterm')

As another commenter said, it is odd syntax. But it works like a champ.

dshendler | November 18, 2006 1:02 AM

Thanks! This helped me a lot.

I put it in camel casing so that it was more obvious for myself, and hopefully for others who find this:

UPDATE tablename SET fieldname = REPLACE(fieldname, 'badterm','goodterm')

As another commenter said, it is odd syntax. But it works like a champ.

dshendler | November 20, 2006 10:28 PM

Eeek-a-rama!

Sounds so simple... but I never realised that function ever existed... now I have a new toy to play with!!!

Ta's a-lot-o...

(Phew... I was about to write/[copy] my own version)

ajos1 | November 24, 2006 10:41 PM

Thanks a lot for the info! Very useful and time-saving!

SiRoCs | February 20, 2007 9:46 AM

Thanks a lot, I was just searching for this.

muztagh | March 10, 2007 7:29 AM

Thanks man - lifesaver ! ;-)

Carl | June 13, 2007 11:39 AM

Wow, thanks so much!

tester | June 26, 2007 4:01 PM

I just wanted to clarify that the replacement works on the string and not the whole field.

Richard | July 31, 2007 5:35 AM

Almost exactly 4 years later: THANK YOU!

Doug | April 18, 2008 5:48 AM

Rent a flat in Paris

Going to the City of lights for a short stay? I have a flat to rent in Paris. Roomy (up to 6 people), comfortable, calm, fully furnished, modern, with broadband internet, private terrasse, garden and parking… You'll love it (I did when I lived there!). Visit appartement-jardin-paris.com for more information and booking.
Paris Web 2015, October 1-3, 2015. Web design, quality and accessibility. I’m going!

Categories

mensuelles Archives

Recent Entries

  • Steve Jobs

    "Remembering that I’ll be dead soon is the most important tool I’ve ever encountered to help me make the big choices in life. Because...

  • Your privacy on MOTOBLUR by Motorola

    After the Nokia Ovi Store carelessness, it's now Motorola who's allowing strangers to get access to your private information on their MOTOBLUR portal. Exactly like...

  • How to resume a broken ADC download

    (I'm documenting this trick for myself to remember, but it can be useful for others…) Apple, on its Apple Developer Connection site, has a bad...

  • WTF is this ‘myEventWatcherDiv’ doing in my web?

    All of a sudden I started to find the following line in most of the web pages I was browsing, including ones I made where...

  • Your privacy on Nokia Ovi Store

    My friend Adam Greenfield recently complained about the over-engineering culture at Nokia: I was given an NFC phone, and told to tap it against the...