MySQL Find and Replace Values

Goal: Find and Replace words in MySQL field

Difficulty: Easy

Prerequisites: Access to run MySQL Update queries

Often times you want to search through an entire column in a MySQL table and do a find and replace on that column. MySQL has a wonderful string function called Replace(). This allows you to pass a field along with a value you wish to find in the field, and replace it with a value of your choice. You can do this on the fly in select statements, but it is more commonly used for updating the tables themselves (at least in my experience).

UPDATE `table_name` SET field_name = replace(field_name,'find this','replace with this');

Another handy feature that MySQL has is using a REPLACE statement. This essentially does the same thing as INSERT except if there is a row with the same primary or unique key, it replaces it with this new record and deletes the old one. While this isn't quite as useful for doing bulk Find and Replaces, it does server a useful purpose when you are trying to determine if you need to create a new record, or update a new one. It should be noted that using this REPLACE method is similar to using INSERT IF NOT EXISTS AND UPDATE IF EXISTS except for the fact that it performs a DELETE operation before the record is recreated.