Azhar Azhar - 22 days ago 10
SQL Question

SQL : get substring from one column and inserting it into other column

First column contains image files names:

abcd.jpg
abcdef.png
...
abcdjs.xyz


I want to 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;