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

by 
select replace(str, rtrim(str, replace(str, "https://stackoverflow.com/", '')), '') 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, "https://stackoverflow.com/", '') 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

More Related Contents:

  1. near “varchar”: syntax error [closed]
  2. How do I make an UPDATE while joining tables on SQLite?
  3. How do I rename a column in a SQLite database table?
  4. How do I check in SQLite whether a table exists?
  5. How to set Sqlite3 to be case insensitive when string comparing?
  6. Escape single quote character for use in an SQLite query
  7. Difference between 2 dates in SQLite
  8. Select random row from a sqlite table
  9. Update with Join in SQLite
  10. Can I read and write to a SQLite database concurrently from multiple connections?
  11. How do I store and retrieve a blob from sqlite?
  12. SQLite error ‘attempt to write a readonly database’ during insert?
  13. Sqlite primary key on multiple columns
  14. Store boolean value in SQLite
  15. Can SQLite support multiple users?
  16. SQLite add Primary Key
  17. Encrypted cookies in Chrome
  18. How delete table inner join with other table in Sqlite?
  19. Export from sqlite to csv using shell script
  20. ALTER TABLE ADD COLUMN IF NOT EXISTS in SQLite
  21. SQLite: How do I save the result of a query as a CSV file?
  22. Create table in SQLite only if it doesn’t exist already
  23. How do DATETIME values work in SQLite?
  24. Add ON DELETE CASCADE behavior to an sqlite3 table after it has been created
  25. Is there a boolean literal in SQLite?
  26. sqlite get ROWID
  27. How to set unique constraint over multiple columns when any one can be null?
  28. How to display blob value using x’abc’ binary string literal syntax?
  29. sql query – how to apply limit within group by
  30. Creating stored procedure in SQLite

Leave a Comment