I have a client with a trainer directory on their site. There is a field in the database that stores the trainer's accolades. One of the accolades is to be considered a Top Trainer. Originally it was a Top 250 trainers list - then the list became 200, then finally Top 100. In the database there are remenants of them being Top 200, 250 and 100. I wanted to replace all of the instances of the Number 250, 200 and 100 and just leave it as "Top Trainer".
I could easily find all of the instances of "Top 250" Trainers by going in to PHPMyadmin and searching
SELECT * FROM trainer WHERE awards LIKE '%Top 250%'
I could just change the 250 to 200 and 100 to see all of the other instances that would need to be changed.
From that list I noticed there were 109 instances that needed to be fixed.
To complicate matters Many of the records had multiple awards. So the record would look something like this
Top 250 Trainer. Best Trainer Ever. Southwest Trainer of the Year
I only need to remove the 250 from the record.
I would need to run my script 3 times to fix all instances; once for Top 250, again for 200 and finally for 100. The way to make the change required using the Mysql REPLACE function.
UPDATE trainer SET awards = REPLACE(awards, 'Top 250', 'Top')
The Replace Function takes 3 parameters, The cell, The Needle (the string being sought), and the replacement text for the found string. So it starts by grabbing the contents of the 'awards' cell, looking for the instance of 'Top 250' and then replacing that instance with the single word 'Top'.
After running that script I ran the next two following scripts to finish up my updates
UPDATE trainer SET awards = REPLACE(awards, 'Top 200', 'Top')
UPDATE trainer SET awards = REPLACE(awards, 'Top 100', 'Top')
Mysql is a powerful database system and knowing how to run scripts to manipulate that data can be a real time saver.
References http://stackoverflow.com/questions/10177208/update-a-column-value-replacing-part-of-a-string
