Mysql Update Part of a String

by John H
2 minutes

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

Finding the "Top" Trainers"

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.

The Fix

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.


Related Articles

Javascript searching through a string

[caption id="attachment_612" align="aligncenter" width="1000"] Searching a string in...

John H John H
4 minutes

MYSQL Maintenance

Are your queries taking a little long?  Are you worried about corrupted data?  Has your slow CSM...

John H John H
~1 minute

Mysql unresponsive after DB Import

Here's your scenario. You've installed mysql on your local machine. Everything was working fine....

John H John H
5 minutes