I am looking to rename all files from .jpg to .jpeg if they exist in the column.

structure:

• tables: table1, table2, ...

Change image1.jpg to image1.jpeg in table1, column1 for the entire column, where some rows may not have the image and some files may already be in the jpeg format

I am very new to mysql, and basically only know how to query and change on a one by one basis, So I am also not sure if this can even be done, but I would really appreciate any help.

CodePudding user response：

Use a like to find all rows where column1 ends in .jpg. Then use regexp_replace to change from .jpg to .jpeg.

Make sure to escape the . in the regex else it will be interpreted as "any character" and will match, for example, "foofjpg". You must use \\ because \ is the escape character in strings.

update some_table
set column1 = regexp_replace(column1, '\\.jpg$', '.jpeg') 
where column1 like '%.jpg'

Demonstration.

CodePudding user response：

UPDATE column value, with use of REPLACE() MySQL function

UPDATE table
SET column = REPLACE(column, '.jpg', '.jpeg')
WHERE column LIKE '%.jpg'

this way you can update column value end with .jpg