Clever Geek Handbook

SQLite: how to select part of a string?

There is a column with filenames: image1.jpg, image12.png, script.php, .htaccess, ...

I only need to choose the file size. I would prefer to do this:

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

but INSTR is not supported in my version of SQLite.

Is there a way to implement this without using the INSTR function?

+3


source to share


2 answers


below is a query (checked and tested) to select only the size of the files. Your filename can contain any number of .

charenters - it will still work

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



column_name

is the name of the column where you have filenames (filenames can have multiple .

table_name

- the name of your table

+7


source


Try the function ltrim(X, Y)

, this is 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 alphabets as second argument, something like



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

which should remove all characters from the left up to .

. If you need a dotless extension, use SUBSTR

. Of course, this means that filenames can contain more than one period.

But I think it is easier and safer to extract the extension in the code that makes the request.

+5


source






More articles:


All Articles