0 0 Votes

MySQL: Search and Replace directly in MySQL database

Tip by Progger99 | 22/02/2012 at 23:44

What many people do not know: You can also 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 simply with the result of the replacement.

If you prefer to write the result of the replacement in another column than the origin 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 "emptyfield" (if there is any).


About the Author

Avatar AuthorThe author has not added a short description to his profile yet.
Show Profile | Message


Related Topics

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 articles on askingbox.com. How to do.