MySQL 中给定子字符串的最后一个索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12775352/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Last index of a given substring in MySQL
提问by Tiny
We can find the index of the first occurrence of a given substring in MySQL using the INSTR()function as follows.
我们可以使用INSTR()如下函数在 MySQL 中找到给定子字符串第一次出现的索引。
SELECT instr('Have_a_good_day', '_') AS index_position
It would display 5, the first occurrence of the specified substring which is in this case an underscore _.
它将显示5指定子字符串的第一次出现，在本例中为下划线_。
I need to obtain the last occurrence of a given character (or a substring) something like the Java lastIndexOf(String str)method of the String class but I can't find any built-in function in MySQL.
我需要获取给定字符（或子字符串）的最后一次出现，类似于lastIndexOf(String str)String 类的 Java方法，但我在 MySQL 中找不到任何内置函数。
Is there any built-in functionality to achieve this in MySQL?
在 MySQL 中是否有任何内置功能可以实现这一点？
回答by curt
@Marc B was close. In MySQL, following statement returns 12:
@Marc B 很接近。在 MySQL 中，以下语句返回 12：
SELECT CHAR_LENGTH("Have_a_good_day") - LOCATE('_', REVERSE("Have_a_good_day"))+1;
Anticipating a possible use of the value, the following statement extracts the left part of the string before the last underscore(i.e., _):
预计该值的可能用途，以下语句提取字符串的最后一个下划线之前的左侧部分（即 _）：
SELECT LEFT("first_middle_last", CHAR_LENGTH("first_middle_last") - LOCATE('_', REVERSE("first_middle_last")));
The result is "first_middle". If you want to include the delimiter, use:
结果是“first_middle”。如果要包含分隔符，请使用：
SELECT LEFT("first_middle_last", CHAR_LENGTH("first_middle_last") - LOCATE('_', REVERSE("first_middle_last"))+1);
It would be nice if they enhanced LOCATE to have an option to start the search from the right.
如果他们增强 LOCATE 以提供从右侧开始搜索的选项，那就太好了。
If you want the right part of the string after the last space a better solution is:
如果您希望在最后一个空格之后使用字符串的正确部分，则更好的解决方案是：
SELECT SUBSTRING_INDEX("first_middle_last", '_', -1);
This returns "last".
这将返回“最后一个”。
回答by N D
If you don't want the overhead of REVERSE use the following:
如果您不想要 REVERSE 的开销，请使用以下内容：
LEFT
(
'Have_a_good_day',
LENGTH('Have_a_good_day') - LENGTH(SUBSTRING_INDEX('Have_a_good_day','_',-1))-1
)
回答by rizalp1
I think you can use substring_index in this way:
我认为你可以这样使用 substring_index ：
select substring_index(string, delimiter,-1)
-1 will start at the end of the string.
-1 将从字符串的末尾开始。
回答by Asped
I found this as a nice trick to do it:
我发现这是一个很好的技巧：
SELECT LOCATE(SUBSTRING_INDEX('Have_a_good_day', '_', -1),'Have_a_good_day')-1 AS indexpos;
This will return the index of the last occurrence (=12). Basically you search for the right part of the string after the last delimiter and then search for the position of this substring in the whole string, which gets you the position :)
这将返回最后一次出现的索引 (=12)。基本上，您在最后一个分隔符之后搜索字符串的右侧部分，然后在整个字符串中搜索此子字符串的位置，从而获得位置:)
If you would like to get the substring to the left of this you can use:
如果你想得到它左边的子字符串，你可以使用：
SELECT
SUBSTRING('Have_a_good_day', 1,
LOCATE(SUBSTRING_INDEX('Have_a_good_day', '_', -1),'Have_a_good_day')-1)
AS sub;
回答by Marc B
Combo of reverse/indexof?
反向/索引的组合？
SELECT LENGTH(string) - SUBSTRING_INDEX(REVERSE(string), delimiter) + 1
breaking it down, given your Have_a_good_day:
分解它，鉴于您的Have_a_good_day：
REVERSE('Have_a_good_day') -> yad_doog_a_evaH
SUBSTRING_INDEX('yad_doog_a_evah', '_') -> 4
LENGTH('Have_a_good_day') -> 15
15 - 4 + 1 -> 12
Have_a_good_day
123456789012345
^
回答by Adewole Kayode
While the above codes work successfully for a single character, they failed when I used them to find the last occurrence of a substring. I therefore recommend the code below for this task:
虽然上述代码对单个字符工作成功，但当我使用它们查找子字符串的最后一次出现时，它们失败了。因此，我为此任务推荐以下代码：
SELECT LENGTH("my father is my father")
- LOCATE('father', REVERSE("my father is my father"))-(LENGTH('father')-1)
This should return 17
这应该返回 17
