How to Find the Last Index of a Substring in MySQL?

MySQL: Finding the Last Index of a Substring

In MySQL, the INSTR() function can be used to determine the index of the first occurrence of a substring. However, if the need arises to ascertain the index of the last occurrence, no built-in function readily addresses this requirement.

Using REVERSE() and LOCATE()

As an alternative approach, a combination of the REVERSE() and LOCATE() functions can be employed to find the last occurrence of a character or substring. The strategy involves reversing the string, identifying the index using LOCATE(), and then subtracting the result from the original string's length.

For example, the following statement retrieves the last index of the underscore character in the string "Have_a_good_day":

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

Extracting the Left Part of the String

To extract the left part of the string before the last occurrence of the delimiter, use:

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

Selecting the Right Part of the String

To isolate the right part of the string after the last occurrence of the delimiter, an alternative method utilizing the SUBSTRING_INDEX() function is recommended:

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

While it would be convenient for LOCATE() to include an option for searching from the right, these techniques provide effective solutions for obtaining the last index of a substring in MySQL.

The above is the detailed content of How to Find the Last Index of a Substring in MySQL?. For more information, please follow other related articles on the PHP Chinese website!