Rightmost occurrence string match in MySQL

Question

I would like to extract the file extension from a field in MySQL that contains filenames. This means I need to find the final '.' character in the field and extract everything after that. The following code example partially works:

SELECT LCASE(RIGHT(filename, LENGTH(filename) - LOCATE('.', filename)))
  FROM mytable;

except that it falls down for cases where the file name contains more than one '.', where it extracts too much. In most programming languages I'd expect to find a function that gives me a rightmost match, but I can't find any such thing for MySQL, nor can I find any discussion from people who have had the same problem and found a workaround.

Tim, please move the accepted answer to Martin's recent answer. My old answer is functional but obviously misguided! Thanks. — mjv
– mjv, Commented Sep 13, 2012 at 19:16

Martin · Accepted Answer · 2012-09-13 07:38:50Z

19

There is the substring_index function - it does exactly what you are looking for:

SELECT substring_index(filename, '.', -1) FROM mytable

answered Sep 13, 2012 at 7:38

Martin

10.9k14 gold badges62 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mjv · Accepted Answer · 2012-09-13 19:18:02Z

2

Edit:
See Martin's answer, using substring_index(), with a negative count parameter is a MUCH better approach!
I'm downvoting myself (actually that's not possible...), upvoting Martin's answer; ' wish I could pass the accepted answer to him... Maybe OP will do that.

Original answer:
The following may do the trick (ATN: length may be off by 1, also may want to deal with case of filename value without a dot character.

SELECT LCASE(RIGHT(filename, LOCATE('.', REVERSE(filename) ) ))
  FROM mytable;

Beware however that this type of post-facto parsing can be quite expensive (read slow), and you may consider extracting the file extension to a separate column, at load time.

edited Sep 13, 2012 at 19:18

answered Sep 17, 2009 at 14:08

mjv

75.7k14 gold badges119 silver badges158 bronze badges

Collectives™ on Stack Overflow

Rightmost occurrence string match in MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related