CODEWITHSUNDEEP
mysqlsqlsearchreplace
julz
julz

Reputation: 3409

MySQL search and replace some text in a field

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.

Upvotes: 306

Views: 255826

Answers (6)

Gaspy
Gaspy

Reputation: 328

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.

Upvotes: 18

thesmallprint
thesmallprint

Reputation: 2391

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

Upvotes: 101

Joe Skora
Joe Skora

Reputation: 14911

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;

Upvotes: 556

Wayne
Wayne

Reputation: 39868

The Replace string function will do that.

Upvotes: 1

Umesh Patil
Umesh Patil

Reputation: 4948

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

Upvotes: 11

basdog22
basdog22

Reputation: 149

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.

Upvotes: 7

Related Questions