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

Determining the Last Occurrence of a Substring in MySQL

MySQL provides the INSTR() function to identify the first instance of a substring within a string. However, finding the last occurrence poses a challenge as there is no built-in function dedicated solely for this purpose.

Solution:

MySQL offers an alternative method to accomplish this task using the LOCATE function and the REVERSE function, as demonstrated below:

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

This expression calculates the last index position of the specified substring, which in this example is the underscore (_).

To obtain the substring before the last occurrence, use:

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

This will return the string "first_middle." To include the delimiter, adjust the query as follows:

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

For locating the substring after the last occurrence, consider the SUBSTRING_INDEX function:

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

This expression will yield the substring "last."

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