Azhar Azhar - 1 month ago 4
MySQL Question

sql - get substring from one column and inserting it into other column

first

coulumn
contains image files names:


abcd.jpg

abcdef.png

abcdjs.xyz


.
I wanna make another
column
which contains extensions of these image files:


jpg

png

xyz


But some image files are named like
ab.gefs.jpg
.

I am not able to take the
string
after the last (.) dot.

Answer

You could reverse the file name, so that extension comes first and reversed, then locate the first dot, then cut everything before the dot -- that would be the reversed extension --, then reverse again:

UPDATE some_table t1, some_table t2
SET t1.some_column = REVERSE(SUBSTRING(REVERSE(t2.filename) FROM 1 FOR LOCATE('.', REVERSE(t2.filename))))
WHERE t1.primary_key = t2.primary_key;
Comments