I find myself having to separate filenames from path extensions quite a bit. This is fiddly using the existing INSTR function as this looks from the start of a string only.

Could we have function that returned the last match of a string as well?

SELECT LAST_INSTR('/home/user1/music/1.mp3','.')

Would give 20 - as would standard 'INSTR' in the above example; since the last and the first are the same in that edge-case.

But:

SELECT LAST_INSTR('/home/user1/./music/1.mp3','.')

Would give 22.

Either that - or a 'reverse' function that just reverses all the characters in a string - then we could all use the standard functions, but backwards.

Or maybe a modified form for the existing INSTR:

instr(X,Y,D)

Where:

• D=0 search forwards
• D=1 search backwards

All return position values should still be output from left-to-right: so you then use it with the existing SUBSTR function etc.

Or finally: none of this (probably) would be needed at all, if the various calls for enhancements for a 'split' function get implemented at some point- as that would (probably) over-rule the need for this.

Maybe a simpler SQLite solution exists but this works anyway:

with path(s) as (select '/home/user1/./music/1.mp3'),
split(l, n) as (select 2, substr(s, -2, 1) from path union all select l+1, substr(s, -l-1, 1) from path, split where n != '.' and l < length(s))
select s FullPath, substr(s, 1, length(s) - max(l)) PathAndName, substr(s, length(s) - max(l) + 2) Extension from path, split

Of course extension functions are better solutions.

An enhanced version would indeed be handy. But when introducing a third parameter "D", it should return the n-th occurrence imho:

• instr(X,Y,0) -> first from left
• instr(X,Y,1) -> second from left, etc.
• instr(X,Y,-1) -> last
• instr(X,Y,-2) -> second-last, etc.

If you're open to using extensions for thi, sqlite-path has path_extension() for a task like you describe:

select path_extension('/home/user1/music/1.mp3'); -- '.mp3'

https://github.com/asg017/sqlite-path

But I would also love to see some more features in the builtin sqlite text processing functions!

I also would love to see something similar to this included in the standard distribution.

Another method to extract the last string component following a separator is (although I can't vouch for efficiency):

replace(string, rtrim(string, replace(string, separator, '')), '')

in your case

replace(path, rtrim(path, replace(path, '.', '')), '')

sqlite> select replace('/home/user1/./music/1.mp3', rtrim('/home/user1/./music/1.mp3', replace('/home/user1/./music/1.mp3', '.', '')),'');
mp3