Search/replace in MySQL field

[Doug Nelson]

Ok, full disclosure, I know nothing about MySQL. It's only through the generosity of others that I've managed my own site for this long.

That said, I have a text field on one table of one database that I need to make a global search an replace of one long string for another, for a couple of thousand records. I basically need to find and replace a single text phrase (5-10 words including spaces) in a description field for every record.

Can this be done via phpmyadmin and some clicking, or would I need a query? And if a query, what (verbatim) would it be?

Thanks in advance.

[Farcaster]

To do this in mySQL you would need to write a query something like the one below. This statement will replace all occurances of "String to be replaced" in the field with "Replacement text". To see more details on how the replace function works, go here.

UPDATE table1
SET field1 = REPLACE (field1, 'String to be replaced','Replacement text')
WHERE field1 LIKE '%String to be replaced%'

And if you want it to ignore case, use this:

UPDATE table1
SET field1 = REPLACE (LOWER(field1), 'string to be replaced','Replacement text')
WHERE field1 LIKE '%String to be replaced%'

[Doug Nelson]

Excellent, that worked a treat. Thank you very much.