MySQL: Search and Replace directly in MySQL database

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