# sql – MySQL search and replace some text in a field

## The Question :

264 people think this question is useful

What MySQL query will do a text search and replace in one particular field in a table?

I.e. search for foo and replace with bar so a record with a field with the value hello foo becomes hello bar.

501 people think this answer is useful

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;



89 people think this answer is useful
UPDATE table_name
SET field = replace(field, 'string-to-find', 'string-that-will-replace-it');



9 people think this answer is useful
 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');



7 people think this answer is useful

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.

6 people think this answer is useful

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.