96

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

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

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.

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

Improve this question
0

7 Answers 7

Reset to default
188

@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".

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

SUBSTRING_INDEX is very useful! do we have something similar in php?
@mikewasmike in php, use explode; end(explode(',', $string)) will get everything in $string after the last comma.
Remember that LENGTH() returns the length in BYTES and if you use these functions on UTF-8 columns, the result maybe will be wrong because UTF-8 character can have 1 to 3 bytes, to be safe to use these functions on several different character sets, use CHAR_LENGTH() instead.
Mind that if the substring is not found, your first statement will return length + 1.
28

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.

Improve this answer

5 Comments

Sure I know I can but it displays the substring itself (after the extraction has been made) and not the index position of that substring. In this case, it would display day but I need to display 12 the index of the substring specified.
How long is your text? Can you reverse it and then use instr or locate functions? I know its not the most ideal way but it would work.
In any case, I can't think of reversing the string because I need to extract other text within the string based on the last index found.
I needed to get everything before, and everything after, the last index of a char in a string, and this helped me do that
Awesome! This works on mysql 5.7 very well!!!! Thanks
25

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
)
Improve this answer

2 Comments

This is exactly what i have and i think is better than using reverse
This differs from the accepted answer in the way that a string without the delimiter, such as Have, becomes an empty string instead of Have.
3

Combo of reverse/indexof?

SELECT LENGTH(string) - SUBSTRING_INDEX(REVERSE(string), delimiter) + 1

breaking it down, given your 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
           ^
Improve this answer

2 Comments

SUBSTRING_INDEX('yad_doog_a_evah', '_') issues an error Incorrect parameter count in the call to native function 'SUBSTRING_INDEX'. It requires a third parameter like SUBSTRING_INDEX('yad_doog_a_evah', '_', 1) which produces a substring yad and not the first index of _ which is 4 in this case. Thanks for the reply.
Do you have a solution. I could do it but in an ugly way which is hateful and avoidable. Thank you.
3

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

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;
Improve this answer

Comments

0

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

Improve this answer

1 Comment

+1 for pointing out that the accepted answer, and others, only work for single characters, but your answer needs modification (you got lucky searching for a pattern that was the last string in your input. Try your soln for 'my' or add text after the last 'father' and it won't work) Try this : SELECT LENGTH("my father is my father") - LOCATE(REVERSE('my'), REVERSE("my father is my father"))-(LENGTH('my')-2)
0

select (length("Have_a_good_day") - instr(reverse("Have_a_good_day"),'_'))) from dual;

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.