Performing substitutions in MySQL

webchalkboard has asked for the wisdom of the Perl Monks concerning the following question:

Hello,

Firstly sorry this isn't strictly a Perl related question (although this problem has arrisen thanks to a bug in my last Perl script :) but i'm sure that people on here will know how to do what i'm trying to do.

Basically I have a database with lots and lots of records in it, there is a field called imageurl, within which I have entries like blablah.jpg. I need to change every imageurl field which ends in .jpg to .gif.

Now I could write a little perl script which does this for me automatically, but i'm sure there must be a way of doing substitions in MySQL directly?

Does anyone here have any experience they could share with me?

Thanks, Tom

Learning without thought is labor lost; thought without learning is perilous. - Confucius

WebChalkboard.com | For the love of art...

Comment on Performing substitutions in MySQL

Replies are listed 'Best First'.
Re: Performing substitutions in MySQL by dragonchild (Archbishop) on Apr 12, 2005 at 13:47 UTC
`UPDATE my_table SET imageurl = CONCAT( LEFT(imageurl, LENGTH(imageurl) - INDEX( imageurl, '.jpg') ) ,'.gif'\ )` [download] You'll have to play with it to make sure there isn't an off-by-one error. My wife's blog	[reply] [d/l]
Re: Performing substitutions in MySQL by kgraff (Monk) on Apr 12, 2005 at 14:00 UTC
MySQL has a bunch of string manipulation functions for example: REPLACE(str,from_str,to_str) Returns the string str with all occurrences of the string from_str replaced by the string to_str. To be able to play around with syntax, make a new column to hold the altered values, then when you get the results you want, delete the original column and rename the new one.	[reply]