8

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.

Aditya Shah
  • 325
  • 2
  • 13

4 Answers4

16

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.

Alvin Teh
  • 778
  • 1
  • 10
  • 17
  • i want data string before last occurrence of _ (underscore) just shown in above example but without passing length. – Aditya Shah May 31 '17 at 06:22
  • From example 1 : **this_is_something** without passing the length – Aditya Shah May 31 '17 at 06:23
  • That's not quite `strrchr` then (as that returns the matched occurrence till the end of the string), do you mind updating the question? – Alvin Teh May 31 '17 at 06:25
  • Actually bro it is :) it returns the portion of haystack which starts at the last occurrence of needle and goes until the end of haystack. – Aditya Shah May 31 '17 at 06:27
  • what server side language you are using ? @AdityaShah – Gaurav Gupta May 31 '17 at 06:29
  • php but i want solution in mysql :) @Gaurav – Aditya Shah May 31 '17 at 06:32
  • 1
    @AdityaShah Your output is from the start of the string to the last occurrence of the substring. That's different. If you're looking for that, the SQL would be `SET @FULL_STRING = 'this_is_something_here'; SELECT LEFT(@FULL_STRING, LENGTH(@FULL_STRING) - LOCATE('_', REVERSE(@FULL_STRING)));` but that is again, not `strrchr`. – Alvin Teh May 31 '17 at 06:37
  • @AlvinTeh it's works fine :) but don't we have any SQL function for that ? – Aditya Shah May 31 '17 at 06:39
  • Because i can't pass this in sql, it's large dynamic query and above is just small part of that. – Aditya Shah May 31 '17 at 06:40
  • @AdityaShah I'm not aware of any single MySQL function that would achieve that. A quick search revealed [this answer](https://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference) which seems to match my guess. If the SQL query is large, consider using stored procedures or user functions instead. – Alvin Teh May 31 '17 at 06:46
  • You can check my answer – RoMEoMusTDiE May 31 '17 at 07:40
  • @maSTAShuFu You're using more than one function as well. @AdityaShah just in case it's not obvious, you don't *absolutely* have to use variables, you can always just substitute `@FULL_STRING` in my code with the actual string. – Alvin Teh May 31 '17 at 07:47
  • That's only a test to substitute the column... more functions but no length as per requirement – RoMEoMusTDiE May 31 '17 at 07:48
  • @maSTAShuFu I think the OP wanted to achieve this with just one function; I did the same earlier (see the earlier comments in this thread). – Alvin Teh May 31 '17 at 07:50
  • 1
    That's ridiculous... then create a scalar function which contains our scripts haha – RoMEoMusTDiE May 31 '17 at 07:56
  • Yeah @AlvinTeh exactly with one function – Aditya Shah May 31 '17 at 08:25
2
select reverse(substr(reverse('this_is_something_here'), 1+locate('_', reverse('this_is_something_here'))));
Kosh
  • 16,966
  • 2
  • 19
  • 34
2

Use reverse, locate, right then replace without using length

Set @str = 'there_is_something';

Select replace(@str,right(@str,locate('_',reverse(@str))),'');
RoMEoMusTDiE
  • 4,739
  • 1
  • 17
  • 26
1

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);
Ronak Patel
  • 651
  • 6
  • 19