Change all filename extensions in table?

[Westech]

I have a table with a field containing image file names. Some end in .jpg and some in .gif. Is there a SQL-only way that I can change all of the extensions to .gif, or will I have to write a program to loop through and handle each one?

I guess basically what I need is to search and replace all instances of .jpg with .gif. Any ideas?

[lilleman]

Hi,

A simple UPDATE query should do the trick.

UPDATE table
SET extension_field = ".gif"
WHERE extension_field = ".jpg"

Yours, Erik.

[Westech]

That would work if the extensions were in separate fields from the rest of the filename, but they're not. The filename and extension are all lumped together as one string. i.e.:

file1.gif
file2.gif
file3.jpg
file4.gif
file5.jpg

[Lats]

Try this...

UPDATE
	table
SET
	filename = replace(filename, '.jpg', '.gif') 
WHERE
	filename LIKE '%.jpg%'

[Westech]

Perfect. I didn't nkow that mySQL had a replace function. Thanks a lot!

[lilleman]

Hi,

MySQL have a lot of built-in functions. Take a look at the manual.

Yours, Erik.