MySQL Bugs: #2051: INSTRREV / LASTINDEXOF

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

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

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.

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.

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)