How to select all individual file name extensions from a file name table?

Question

I have a table of ~ 20k file names. How to choose a list of individual extensions? The file name extension can be considered a 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 that he will start the search for '.' on the right side.

Answer 2

There is a very cool and powerful feature in MySQL and other databases - it is the ability to include regular expression syntax when choosing sample data

SELECT something from table WHERE column REGEXP 'regexp'

see this http://www.tech-recipes.com/rx/484/use-regular-expressions-in-mysql-select-statements/

so that you can write a template to choose what you want.

Answer 3

The answer given by @bnvdarklord is right, but it will include filenames that also do not have extensions in the result set, so if you want only extension templates to use below the query.

 SELECT DISTINCT substring_index(column_containing_file_names,'.',-1) FROM table where column_containing_file_names like '%.%';