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.

Nawab

Answer 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)

Inseverable

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

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

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

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/… – Cycloparaffin

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

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

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? – Homorganic

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. – Flatten

Answer 2

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

Smallpox

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

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. – Tubular

Answer 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.

Accost

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' – Feudal

nice. just replaced http: with https: on about 1000 rows. super easy – Indoor

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

Wallsend

Answer 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.

Yeryerevan

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

Answer 6

The Replace string function will do that.

Judsonjudus

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. – Slag

Answer 7

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.

Dogooder