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.
Add a comment
|
6 Answers
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;
8 Comments
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?
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?
|
UPDATE table_name
SET field = replace(field, 'string-to-find', 'string-that-will-replace-it');
2 Comments
Anantha Raju C
Helped Me. For all the noobs, please remove the square brackets.
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.
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.
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');
Comments
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.
1 Comment
200_success
If you quote the field name, make sure you use the right kind of quotes!
The Replace string function will do that.
1 Comment
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.