Bug #12699 SUBSTRING_INDEX() enhancement
Submitted: 21 Aug 2005 8:35 Modified: 15 Dec 2010 10:27
Reporter: Ondra Zizka Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: General Severity:S4 (Feature request)
Version: OS:Any (All)
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[21 Aug 2005 8:35] Ondra Zizka
SUBSTRING_INDEX could be enhanced this way:

SUBSTRING_INDEX(str,delim,count, opposite)
If "opposite" evals to true, SUBSTRING_INDEX will return the other part of the string that it would return normally:

SELECT SUBSTRING_INDEX('www.winlyrics.com', '.', 2);
        -> 'www.winlyrics'
SELECT SUBSTRING_INDEX('www.winlyrics.com', '.', -2);
        -> 'winlyrics.com'

SELECT SUBSTRING_INDEX('www.winlyrics.com', '.', 2, 1);
        -> 'com'
SELECT SUBSTRING_INDEX('www.winlyrics.com', '.', -2, 1);
        -> 'www'

Actually this gets "string TO the second LAST delimiter",
and "string FROM the second delimiter".
It would be useful for cases when the number of delimiters is unknown.
Currently you can achieve this by calling (at least) LENGTH twice and SUBSTRING once.

How to repeat: