Clever Geek Handbook

SQLite: how to select only filename without extension?

I need to extract the filename without extension from one of the tables I have.

I currently use

SELECT filename FROM files
Used by

which returns the fully qualified filename (Jessica.Timber.mp3). So is it possible to get just the filename using sqlite (ex: Jessica.Timber)? Files can contain multiple "." but only the last dot followed by ext should be removed.

I tried the following query which gives the result if the extension is only 3 letters long (ex: * .mp3), but it fails if there is more than this (ex: * .flac)

SELECT substr(filename, -4, -100) from files;
+3


source to share


2 answers


Below is the request - you will get the filename without the extension, regardless of the length of the extension. The file name can contain any number of characters .

(checked and verified)

select replace(filename
             , '.' || replace(filename
                            , rtrim(filename, replace(filename, '.', '') )
                            , '')
             , '')
  from files;

Test:



create table files (filename);
insert into files values ("ph.otoJpg.jpg"), ("ph.otoJpeg.jpeg");

Then the above code gives:

ph.otoJpg.
ph.otoJpeg.
+3


source


SQLite has no built-in string functions to help with this.



It would be possible to create a recursive CTE, but the simplest way is to get the full filename and remove the extension in your code.

0


source






More articles:


All Articles