Question 21480 in Mysql Question posted in
The official MySQL documentation can be found here.

Renaming jpg to jpeg in mysql

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

structure:

  • tables: table1, table2, …
column1 column2
image1.jpg id1
image2.jpg id2
image3.jpeg id3
id4

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search