如何在 SQLite 中获取子字符串的最后一个索引? [英] How to get the last index of a substring in SQLite?

问题描述

我在表中有一个字符串:http://www.foo.com/hello/bar/baz 并且我想获取最后一个/"之后的所有内容(在此案例字符串'baz').

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').

我可以做 substr(str, lastIndexOf(str, '/'))，但我不知道如何在 SQLite 中获取 lastIndexOf.

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

推荐答案

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

replace(str, '/', '') 从 str 中删除 / 字符，所以我们将有:

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

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

我们称之为noslashes.接下来我们使用 rtrim(str, noslashes)，它将删除出现在 noslashes 中的所有字符，从右侧开始.因为 noslashes 包含字符串中除 / 之外的所有内容，所以它会从右边修剪直到找到 / 字符.这样我们就找到了我们的父目录:

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 微克/

现在我们使用 replace 从文件路径中删除父路径名，我们只有文件名

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

1200 麦克风 - 03 - Mescaline.mp3

这篇关于如何在 SQLite 中获取子字符串的最后一个索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！