MySQL中给定子字符串的最后一个索引 [英] Last index of a given substring in MySQL

问题描述

我们可以使用INSTR()函数来找到MySQL中给定子字符串首次出现的索引.

We can find the index of the first occurrence of a given substring in MySQL using the INSTR() function as follows.

SELECT instr('Have_a_good_day', '_') AS index_position

它将显示5，这是指定子字符串的首次出现，在此情况下为下划线_.

It would display 5, the first occurrence of the specified substring which is in this case an underscore _.

我需要获取给定字符(或子字符串)的最后一次出现，例如Java

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.

在MySQL中是否有内置功能可以实现这一目标?

Is there any built-in functionality to achieve this in MySQL?

推荐答案

@Marc B关闭了.在MySQL中，以下语句返回12:

@Marc B was close. In MySQL, following statement returns 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")));

结果是"first_middle".如果要包括定界符，请使用:

The result is "first_middle". If you want to include the delimiter, use:

SELECT LEFT("first_middle_last", CHAR_LENGTH("first_middle_last") - LOCATE('_', REVERSE("first_middle_last"))+1);

如果他们增强了LOCATE功能，并且可以选择从右侧开始搜索，那就太好了.

It would be nice if they enhanced LOCATE to have an option to start the search from the right.

如果您要在最后一个空格后的字符串的右侧部分，一个更好的解决方案是:

If you want the right part of the string after the last space a better solution is:

SELECT SUBSTRING_INDEX("first_middle_last", '_', -1);

这将返回"last".

This returns "last".

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