CODEWITHSUNDEEP
mysqlsqlregex
Azhar
Azhar

Reputation: 91

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.

Upvotes: 1

Views: 735

Answers (2)

ardavey
ardavey

Reputation: 161

MySQL's SUBSTRING_INDEX() function is pretty much tailor made for what you're trying to do:

> select substring_index( 'foo.png', '.', -1 );
+---------------------------------------+
| substring_index( 'foo.png', '.', -1 ) |
+---------------------------------------+
| png                                   |
+---------------------------------------+
1 row in set (0.01 sec)

> select substring_index( 'foo.bar.jpg', '.', -1 );
+-------------------------------------------+
| substring_index( 'foo.bar.jpg', '.', -1 ) |
+-------------------------------------------+
| jpg                                       |
+-------------------------------------------+
1 row in set (0.00 sec)

Upvotes: 3

borowis
borowis

Reputation: 1250

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;

Upvotes: 3

Related Questions