MySQL search and replace some text in a field

Question

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.

Solution 1

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)

Solution 2

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

Solution 3

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.

Solution 4

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

Solution 5

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.

View more solutions

Comments

[Gruber]

Works for me. It depends on how you interpret the question. If you need the database entries to change, then use update. Otherwise this solution is much better as it can be used without updating fields.

[Meetai.com]

UPDATE [table_name] SET [field_name] = REPLACE([field_name], "foo", "bar");

[inemanja]

I think it is faster not to use WHERE instr(field, 'foo') > 0; (so it would not perform 2 searches)... Am I wrong?

[SBhojani]

Can anyone comment on @inemanja's question? Would it be faster without the 'WHERE` clause?

[user898763452]

Would this 'where' clause not mistakenly skip records with the string you're searching for occurring at position 0 in the field? "INSTR(str,substr) Returns the position of the first occurrence of substring substr in string str." dev.mysql.com/doc/refman/5.0/en/…

[Alexis Wilke]

@treddell, no positions start at 1 in SQL strings.

[Alexis Wilke]

@inemanja, @Air without the WHERE clause you do an UPDATE on all the rows...

[pdwalker]

Like Pring, if you're going to leave a comment like that, you might want to explain why. Was it a mistake in the original advice, or a mistake on your part? And you do know that before you make any sweeping changes to a database you are supposed to back it up first?

[Anantha Raju C]

Helped Me. For all the noobs, please remove the square brackets.

[wosevision]

I can offer one more practical use for the full (plus WHERE INSTR (...)) version of this query: if you're simulating the query e.g. with a tool like PHPMyAdmin, the version that omits the WHERE clause returns every row in the database for the simulation. Not very useful for determining what's going to change.

[Max]

Works for me because i need add another clause where. UPDATE table_name SET field = REPLACE(field, 'foo', 'bar') WHERE field LIKE '%foo%' AND otherfield='foo22'

[200_success]

If you quote the field name, make sure you use the right kind of quotes!

[Jonathan Ben-Avraham]

Note to the unwary: In this answer replace "field" with the name of the field that you want to modify. See answers below for clearer presentation of the syntax. In this answer, "field" is not an SQL keyword.