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 | Répondre

You just saved my night :)

ap | April 27, 2004 11:48 PM | Répondre

Thanks. Works great...

z33k | June 18, 2004 5:09 AM | Répondre

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

h8R | December 11, 2005 4:20 PM | Répondre

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 | Répondre

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 | Répondre

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 | Répondre

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 | Répondre

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

SiRoCs | February 20, 2007 9:46 AM | Répondre

Thanks a lot, I was just searching for this.

muztagh | March 10, 2007 7:29 AM | Répondre

Thanks man - lifesaver ! ;-)

Carl | June 13, 2007 11:39 AM | Répondre

Wow, thanks so much!

tester | June 26, 2007 4:01 PM | Répondre

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

Richard | July 31, 2007 5:35 AM | Répondre

Almost exactly 4 years later: THANK YOU!

Doug | April 18, 2008 5:48 AM | Répondre

Leave a comment

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.

Categories

mensuelles Archives

Recent Entries

  • On Apple Safari's use of justified text in Reader

    On my professional blog, a take on Apple Safari's use of justified text in Reader....

  • Curated computing

    Perhaps the most pernicious proposition of the “everything must be open” crusade is the notion that curation is bad and anti-freedom. Soldiers of this crusade...

  • Death by Apple, the obsolescence of Flash

    Flash was created during the PC era – for PCs and mice. Flash is a successful business for Adobe, and we can understand why they...

  • Ogg (and Mozilla) objections

    Ogg objections by Måns: When challenged, three types of reaction are characteristic of the Ogg campaigners. On occasion, these people will assume an apologetic tone,...

  • Paris Web 2010 — Call for speakers

    (Disclaimer: I manage the communication for Paris Web, this is a copy of the official call for speakers.) Hello all, Paris Web is a French...