How to select all distinct filename extensions from table of filenames?

Question

I have a table of ~20k filenames. How do I select a list of the distinct extensions? A filename extension can be considered the case insensitive string after the last .

Answer 1

You can use substring_index:

SELECT DISTINCT substring_index(column_containing_file_names,'.',-1) FROM table

-1 means it will start searching for the '.' from the right side.