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.

Answer 1

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.