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

sqlite

Solution

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`

Problem

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.

Original source