How to get the last index of a substring in SQLite?

Question

I have a string in the table: http://www.foo.com/hello/bar/baz and I want to get everything that comes after the last '/' (in this case the string 'baz').

I can do substr(str, lastIndexOf(str, '/')), but I dont know how to get the lastIndexOf in SQLite.

Answer 1

select replace(str, rtrim(str, replace(str, '/', '')), '') from table;

Step-by-step explanation. For example, we have the string:

/storage/udisk/1200 Mics/[2002] 1200 Micrograms/1200 Mics - 03 - Mescaline.mp3

The replace(str, '/', '') removes / chars from str so we will have:

storageudisk1200 Mics[2002] 1200 Micrograms1200 Mics - 06 - Ecstasy.mp3

Let's call this noslashes. Next we use rtrim(str, noslashes), which will remove all chars that appear in noslashes, starting from the right. Because noslashes contains everything in the string except /, this will trim from the right until it finds the / char. This way we found our parent dir:

/storage/udisk/1200 Mics/[2002] 1200 Micrograms/

Now we remove the parent path name from the file path using replace and we have just the filename

1200 Mics - 03 - Mescaline.mp3

Answer 2

There are limits to sqlite core functions' expressive power. For a known maximum number of / characters, this can be accomplished by a series of nested substr() and instr() calls, removing parts of the string to the next / at a time, but that's not too elegant.

You should really do it in your programming language instead.