Replace part with sql only

Question

I have a big list of images in my database.

the images are all jpg now.

They used to be jpg and png so i saved the extension in my database.

Now i want to delete all the extensons so everything after the dot (including the dot).

Is this possible with sql.

I think i should use something like :

update Where row like '%.jpg' or '%.JPG' or '%.png' or '%.PNG' set value row ..

but i cant find anything. is this possible with sql?

I know i can do it with php, with explode and then just echo part[0] but i want to change it in my database so everyting looks alot neater.

Answer 1

Yes this is possible. The syntax is:

update <table>
    set value = substring_index(row, '.', 1)
    Where row like '%.jpg' or row like '%.JPG' or row like '%.png' or row like '%.PNG' 

This assumes there is only one '.' in the name.

EDIT:

If . can appear in the name more than once, you can use this trick:

update <table>
    set value = substring_index(row, '.',
                                length(row) - length(replace(row, '.', ''))
                               )
    Where row like '%.jpg' or row like '%.JPG' or row like '%.png' or row like '%.PNG' 

The expression length(row) - length(replace(row, '.', '') counts the number of periods in the row.

You can also express the where as:

    where substring_index(row, '.', -1) in ('jpg', 'JPG', 'png', 'PNG')