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

Question

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.

See [*How to extract a file extension in PHP?*](http://stackoverflow.com/questions/173868/how-to-extract-a-file-extension-in-php). — Wiktor Stribiżew, Oct 31 '16 at 14:14
i know how to do it in php but i wanna know how to do it in mysql @WiktorStribiżew — Azhar, Oct 31 '16 at 14:17
The regex for finding everything after the final period `.` is `[^.]+$`. So try it out at http://regexr.com/ — MonkeyZeus, Oct 31 '16 at 14:20
mysql doesn't support capturing groups so regex seems useless here: it's either matched or not matched in mysql — borowis, Oct 31 '16 at 14:22

borowis · Accepted Answer · 2016-10-31T14:29:27.597

3

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;

edited Oct 31 '16 at 14:29

answered Oct 31 '16 at 14:19

borowis

1,207
10
17

1

I like this answer but based on OP's question this should probably be an update statement. – MonkeyZeus Oct 31 '16 at 14:23
1

thanks, I updated my answer to use `UPDATE` instead of `INSERT INTO` – borowis Oct 31 '16 at 14:30
1

@Azhar, I actually didn't know about the `SUBSTRING_INDEX()` function, seems like that's a better way to do that : ) – borowis Oct 31 '16 at 14:43
1

All of that `REVERSE`ing made my head spin! ;-) – ardavey Oct 31 '16 at 14:44
ha! you bet :-) – borowis Oct 31 '16 at 14:46

score 3 · Answer 2 · answered Oct 31 '16 at 14:36

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)

yeah, this seems like much better way than reversing a string 2 times :) thanks, @ardavey — borowis, Oct 31 '16 at 14:44

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

2 Answers2