CODEWITHSUNDEEP
sqlite
user3742985
user3742985

Reputation: 65

SQLite Select Last Occurance of Character

I'm using SQLite, and I'm unable to find a way to locate the index of the last occurrence of a character. For example, the records that I need to parse are:

test123.contoso.txt
testABC.contoso.atlanta.docx
another.test.vb

I would appreciate if anybody can point me in the direction how I can parse the file extensions (txt, docx, vb) from these records through a SQLite query. I've tried using the REVERSE function, but unfortunately SQLite doesn't include this in it's toolbox.

Upvotes: 2

Views: 3390

Answers (2)

user1461607
user1461607

Reputation: 2770

You can adapt the solution in How to get the last index of a substring in SQLite? to extract the extension.

select distinct replace(file, rtrim(file, replace(file, '.', '')), '') from files;

Upvotes: 1

CL.
CL.

Reputation: 180300

If you want to check whether a file name has a specific extension, you can use LIKE:

... WHERE FileName LIKE '%.txt'

However, it is not possible with the built-in functions to extract the file extension.

If you need to handle the file extension separately, you should store it separately in the database, too.

Upvotes: 0

Related Questions