Replacing Text In A MySQL Database

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.