2

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.

user3742985
  • 65
  • 4
  • Are you trying to SELECT out the file extension or do a WHERE condition using the file extension or something entirely different? If the latter, try `REGEXP` – Mark Silverberg Jul 24 '14 at 00:53
  • 1
    I'm trying to do a SELECT based on the file extension, for example I would like to to a SELECT DISTINCT and be able to determine all of the file extensions among the files in all of the records. – user3742985 Jul 24 '14 at 00:55
  • Yeah - there doesnt seem to be a remotely clean way to do this with SQLite. I would suggest doing it in your app code or starting to store the file extension separately – Mark Silverberg Jul 24 '14 at 01:01

2 Answers2

1

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;

user1461607
  • 2,092
  • 1
  • 22
  • 21
0

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.

CL.
  • 165,803
  • 15
  • 203
  • 239