SQLite: How to select part of string?

Question

There is table column containing file names: image1.jpg, image12.png, script.php, .htaccess,...

I need to select the file extentions only. I would prefer to do that way:

SELECT DISTINCT SUBSTR(column,INSTR('.',column)+1) FROM table

but INSTR isn't supported in my version of SQLite.

Is there way to realize it without using INSTR function?

Answer 1

below is the query (Tested and verified) for selecting the file extentions only. Your filename can contain any number of . charenters - still it will work

select distinct replace(column_name, rtrim(column_name, replace(column_name, '.', '' ) ), '') from table_name;

column_name is the name of column where you have the file names(filenames can have multiple .'s

table_name is the name of your table

Answer 2

Try the ltrim(X, Y) function, thats what the doc says:

The ltrim(X,Y) function returns a string formed by removing any and all characters that appear in Y from the left side of X.

List all the alphabet as the second argument, something like

SELECT ltrim(column, "abcd...xyz1234567890") From T

that should remove all the characters from left up until .. If you need the extension without the dot then use SUBSTR on it. Of course this means that filenames may not contain more that one dot.

But I think it is way easier and safer to extract the extension in the code which executes the query.