[SOLVED] Help, How to change batch of *.bmp to *.jpg in database

[cTak]

Hello, I am helping a friend setup an eCommerce store with Zen cart, he added all of the products and uploaded the images, but a lot of them are .bmp files. 

I have downloaded them and converted them to web friendly jpgs, but the database is still calling for the .bmp files.

 

Anyone have an SQL code I can use to update the "product_images" coulum so that *.bmp is changed to *.jpg?

 

Something like this, but this does not work because there is a filename in front of the extension.

UPDATE zen_products SET products_image = "jpg" WHERE products_image = "bmp"

 

 

Thanks!

[fenway]

You can use a regex/substring() to see if this piece appears at the end of the string, and then simply replace it.

[cTak]

Thanks for the reply.

 

I am fairly new to this but learning quickly.  Can you give me an example of a command to run?

 

Something that would change *.gif to *.jpg ?

[bubblegum.anarchy]

SELECT replace(col_name, '.gif', '.jpg') FROM tab_name;

[cTak]

Showing rows 0 - 29 (3169 total, Query took 0.0005 sec)  
SQL query: 
SELECT REPLACE (
`products_image` ,
'.gif',
'.jpg'
)
FROM zen_products
LIMIT 0 ,
30  

 

I did what you said bubblegum.anarchy, it shows the first 30 rows changed, but it doesn't save the changes?  What do I do from here?

[bubblegum.anarchy]

Use the UPDATE statement (backup the table first):

 

UPDATE zen_products SET products_image = replace(products_image, '.gif', '.jpg')

[fenway]

Use the UPDATE statement (backup the table first):

 

UPDATE zen_products SET products_image = replace(products_image, '.gif', '.jpg')

 

Strictly speaking, that's not very robust, since that substring could appear anywhere in the products_image string (although unlikely) -- hence my suggestion of a regex.

[bubblegum.anarchy]

Strictly speaking, that's not very robust, since that substring could appear anywhere in the products_image string (although unlikely) -- hence my suggestion of a regex.

 

Yes, off coarse fenway - I recently developed a habit of naming images with dot seperators like btn.edit.gif so an image named btn.giffer.gif would end up being named btn.jpgfer.jpg - but as you suggest, unlikely.

 

MySQL really should apply full regular expressions so something like UPDATE table SET value = REGEXP '/(.+)\.gif$/\1.jpg/i' could be performed.

[fenway]

True, but the where clause could simply make sure that the substring was 4 characters from the end before the update.