22 Votes

MySQL: Search and Replace directly in MySQL database

Tip by Progger99 | Last update on 2022-04-30 | Created on 2012-02-22

What many people do not know: You can perform replacements directly in your MySQL database. This saves time and ressources for reading, editing and re-inserting of the values with PHP and is therefore much more faster. That is, how it works:

UPDATE tab SET col = REPLACE(col, 'search', 'replace');

The MySQL function REPLACE() expects three parameters: the appropriate column as well as the search and the replace string. In the example we are replacing in the column "col" all occurrences of "search" with "replace". Thereby, we overwrite the old contents of the column "col" simply with the result of the replacement.

How to write the result of the replacement into another column

If you prefer to write the result of the replacement in another column than the original one, you can use this MySQL command:

UPDATE tab SET emptycol = REPLACE(col, 'search', 'replace');

With this request, first we replace all occurences of "search" with "replace" in the column "col" of our table. After that we are saving the result to the column "emptycol". This keeps the content of "col" and overwrites the old content of "emptycol" (if there is any).

00 Votes

I didn't even know that this is possible! While I used MySQL only as a pure medium for saving values in the past, it becomes more and more clear to me how powerful MySQL actually is and what you can do with it! Such functions really save a lot of time! Brilliant!
2022-04-24 at 09:28

ReplyPositive Negative

About the Author

AvatarThe author has not added a profile short description yet.
Show Profile


Related Topics

The Askingbox Search

Info | 0 Comments

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.


Ask your own question or write your own article on askingbox.com. That’s how it’s done.