CODEWITHSUNDEEP
mysqlstring
Aditya Shah
Aditya Shah

Reputation: 325

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

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.

Upvotes: 8

Views: 8420

Answers (4)

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

Use reverse, locate, right then replace without using length

Set @str = 'there_is_something';

Select replace(@str,right(@str,locate('_',reverse(@str))),'');

Upvotes: 2

Kosh
Kosh

Reputation: 18393

select reverse(substr(reverse('this_is_something_here'), 1+locate('_', reverse('this_is_something_here'))));

Upvotes: 2

Alvin Teh
Alvin Teh

Reputation: 787

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.

Upvotes: 16

Ronak Patel
Ronak Patel

Reputation: 671

You can write query like this 

SELECT SUBSTRING_INDEX('and_this_may_go_like_this_too','_',(LENGTH('and_this_may_go_like_this_too')-LENGTH(REPLACE('and_this_may_go_like_this_too' ,'_',''))) - 1);

Upvotes: 1

Related Questions