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.

score 81 · Accepted Answer · edited Jul 22 '19 at 11:13

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

@ungalcrys Great! If you change your `'/'` to `'\'` you can split a Windows FullFileName to the FileName only. If cut away the first `replace(str,` and the ending `, '')` you get the Path without FileName. — PeterCo, Mar 23 '18 at 17:41

WonderWorker · Answer 2 · 2017-06-16T09:59:26.317

1

The solution is to replace all occurances of FilePath and TableName in the code with the names as they appear in your table.

You will need to create a REVERSE function e.g. in PHP you would run the following command:

conn.create_function("REVERSE", 1, lambda s: s[::-1])

Note: Each language has its own way of creating custom SQLite commands.

REVERSE the VARCHAR, and then once the processing has finished REVERSE it back again.

SELECT 
    FilePath, 
    REVERSE(SUBSTR(REVERSE(FilePath), INSTR(REVERSE(FilePath), '\'), LENGTH(FilePath))) AS [DirectoryPath], 
    REVERSE(SUBSTR(REVERSE(FilePath), 0, INSTR(REVERSE(FilePath), '\'))) AS [Filename] 
FROM 
    TableName

edited Jun 16 '17 at 09:59

answered Jan 18 '17 at 13:27

WonderWorker

8,539
4
63
74

Did you notice the sqlite tag? – laalto Jan 18 '17 at 21:00
1

Yes. That's why this is valid and tested SQLite SQL – WonderWorker Jan 23 '17 at 08:58
4

@Knickerless-Noggins Except SQLite doesn't have CHARINDEX nor REVERSE functions. – RReverser Jun 15 '17 at 16:04

score 0 · Answer 3 · answered Jan 27 '14 at 18:48

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.

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

3 Answers3

Linked

Related