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.

The Question Comments :

The Answer 1

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;

The Answer 2

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

The Answer 3

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');

The Answer 4

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.

The Answer 5

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.

The Answer 6

1 people think this answer is useful

The Replace string function will do that.

The Answer 7

0 people think this answer is useful

I used the above command line as follow: update TABLE-NAME set FIELD = replace(FIELD, ‘And’, ‘and’); the purpose was to replace And with and (“A” should be lowercase). The problem is it cannot find the “And” in database, but if I use like “%And%” then it can find it along with many other ands that are part of a word or even the ones that are already lowercase.

Add a Comment