Bug #2051 INSTRREV / LASTINDEXOF
Submitted: 8 Dec 2003 16:24 Modified: 10 Dec 2005 17:20
Reporter: Arjen Lentz Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[8 Dec 2003 16:24] Arjen Lentz
Description:
Alex Hess wrote: 

> Where is INSTRREV / LASTINDEXOF?
> It seems the function to search a string starting from the end is missing.
> In VB/ASP, it's called InStrRev, in javascript it's lastIndexOf.
> 
> You can fake it by doing this:
>   SELECT LENGTH("The string to, search!") - 
>          INSTR(REVERSE("The string to, search!"), ",") + 1
> 
> but this is hardly practical. Ideally:
>   InStrRev(STRING_TO_SEARCH, SEARCH_FOR, START_AT_POSITION)
> 
> Where leaving out START_AT_POSITION or specifying -1 will begin at the end
> of the string and search towards the beginning.

How to repeat:
You can fake it by doing this:
   SELECT LENGTH("The string to, search!") - 
          INSTR(REVERSE("The string to, search!"), ",") + 1
 
 but this is hardly practical. 

Suggested fix:
Implement INSTRREV / LASTINDEXOF
[27 Nov 2005 12:12] Valeriy Kravchuk
Thank you for a feature request. Please, check the SUBSTRING_INDEX(str,delim,count) function already implemented (read http://dev.mysql.com/doc/refman/5.0/en/string-functions.html for details). I think it can be used to implement a kind of search you are looking for.
[28 Nov 2005 2:47] Arjen Lentz
No, SUBSTRING_INDEX() can merely return either everything before a certain position or after a certain position.
The original feature request was about *searching* a string from the end rather the start.
[10 Dec 2005 17:20] Valeriy Kravchuk
The following workaround may be simpler (2 function calls only), but I agree, that it will be a useful feature to have for some kinds of string processing.

mysql> select instr("The string to, search!", substring_index("The string to, se
arch!", ",", -1)) - 1 col;
+-----+
| col |
+-----+
|  14 |
+-----+
1 row in set (0.00 sec)