CODEWITHSUNDEEP
sqlite
chacham15
chacham15

Reputation: 14251

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

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.

Upvotes: 39

Views: 18639

Answers (4)

user1987509
user1987509

Reputation:

The answer given by ungalcrys works great;

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

but I have 399 records that I want to replace with the value returned.

Not sure how I would do this.

I tried;

update Part set Filename = (select replace(Part.Filename, rtrim(Part.Filename, replace(Part.Filename, '\', '')), '') from Part)

but it replaced all records with a single value.

Upvotes: 0

ungalcrys
ungalcrys

Reputation: 5610

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

Upvotes: 101

WonderWorker
WonderWorker

Reputation: 9062

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

Upvotes: 0

laalto
laalto

Reputation: 152807

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.

Upvotes: 0

Related Questions