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

类似于 php 中的 strrchr

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)

在 MySQL 中是否有任何内置功能可以实现此目的？

在此先感谢 friend 们。

Answer 1

我不太明白你的例子，但我认为你想要的是传递 -1 作为长度并在前面加上子字符串。

比较

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

如果您正在寻找字符串的之前和针之前的部分，并且不是从针到字符串末尾的部分，您可以使用:

SET @FULL_STRING = 'this_is_something_here';  

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

请注意，第二个语句不是strrchr 所做的。