MySQL search and replace some text in a field

Question

Change table_name and field to match your table name and field in question:

UPDATE table_name SET field = REPLACE(field, 'foo', 'bar') WHERE INSTR(field, 'foo') > 0;

• REPLACE (string functions)
• INSTR (string functions)

UPDATE table_name 
SET field = replace(field, 'string-to-find', 'string-that-will-replace-it');

 UPDATE table SET field = replace(field, text_needs_to_be_replaced, text_required);

Like for example, if I want to replace all occurrences of John by Mark I will use below,

UPDATE student SET student_name = replace(student_name, 'John', 'Mark');

In my experience, the fastest method is

UPDATE table_name SET field = REPLACE(field, 'foo', 'bar') WHERE field LIKE '%foo%';

The INSTR() way is the second-fastest and omitting the WHERE clause altogether is slowest, even if the column is not indexed.

And if you want to search and replace based on the value of another field you could do a CONCAT:

update table_name set `field_name` = replace(`field_name`,'YOUR_OLD_STRING',CONCAT('NEW_STRING',`OTHER_FIELD_VALUE`,'AFTER_IF_NEEDED'));

Just to have this one here so that others will find it at once.

The Replace string function will do that.