MYSQL : Find the last occurrence of a character in a string

Question

Length will be dynamic and i want to find the data before last occurrence of a character in a string in MYSQL

Like strrchr in php

To get last occurrence of _ (underscore) I need to pass length. and here it's 3

mysql> SELECT SUBSTRING_INDEX ('this_is_something_here', '_', 3);

+----------------------------------------------------+
| SUBSTRING_INDEX ('this_is_something_here', '_', 3) |
+----------------------------------------------------+
| this_is_something                                  |
+----------------------------------------------------+

And here, to get last occurrence of _ (underscore) i need to pass length. and here it's 6

 

mysql> SELECT SUBSTRING_INDEX ('and_this_may_go_like_this_too', '_', 6);
+-----------------------------------------------------------+
| SUBSTRING_INDEX ('and_this_may_go_like_this_too', '_', 6) |
+-----------------------------------------------------------+
| and_this_may_go_like_this                                 |
+-----------------------------------------------------------+

i want data string before last occurrence of _ (underscore) just shown in above example but without passing length.

Note : from above example i want before data of "_here" and "_too"

last occurrence of _ (underscore)

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

Thanks in advance amigos.

Answer 1

I didn't quite get your examples, but I think what you want is to pass -1 as the length and prepend the substring prior.

Compare

strrchr('and_this_may_go_like_this_too', '_'); // Returns _too

SELECT SUBSTRING_INDEX('and_this_may_go_like_this_too', '_', -1);
-- Returns too, just need to concatenate `_` so...
SELECT CONCAT('_', SUBSTRING_INDEX('and_this_may_go_like_this_too', '_', -1));
-- Returns _too

If you're looking for the part of the string before and up to the needle, and not from the needle to the end of the string, you can use:

SET @FULL_STRING = 'this_is_something_here';  

SELECT LEFT(@FULL_STRING, LENGTH(@FULL_STRING) - LOCATE('_', REVERSE(@FULL_STRING)));
-- Returns this_is_something

Note that the second statement is not what strrchr does.