Last index of a given substring in MySQL

Solution 1:

@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")));

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);

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);

This returns "last".

If you don't want the overhead of REVERSE use the following:

LEFT
(
   'Have_a_good_day', 
   LENGTH('Have_a_good_day') - LENGTH(SUBSTRING_INDEX('Have_a_good_day','_',-1))-1
)

I think you can use substring_index in this way:

select substring_index(string, delimiter,-1)

-1 will start at the end of the string.