There are times when you find yourself having to rename data in your MySQL database based on a specific pattern.
I had this very problem today. A designer had changed the dimensions of the thumbnails on a website and needed the data in the database updated to reflect the new path if they were stored in a specific directory.
My first thought was to write a script to iterate through every row in the database, extract the data, manipulate it, then write it back. Thankfully, warning bells rang in my head and I thought about the problem again.
MySQL offers various string manipulate routines and the REPLACE
takes a string, a substring to match, and a replacement to insert if a match occurs.
UPDATE artists SET thumbnail_url=REPLACE(thumbnail_url, '/160x90/','/150x84/') WHERE thumbnail_url IS NOT NULL;
This SQL goes through each row in the artists table where the thumbnail_url is not null, replacing each instance of /160×90/ with /150×84/, keeping the other data intact.