Syntax
SUBSTRING_INDEX(str, delim, count)
Purpose
Returns a substring from the string str before the delim delimiter and the count occurrence.
If
countis positive, it returns the substring before thecountth occurrence of the delimiter, starting from the left.If
countis negative, it returns the substring after thecountth occurrence of the delimiter, starting from the right.If any parameter is
NULL, it returnsNULL.If
strordelimis an empty string, it returns an empty string.If
count=0, it also returns an empty string.The
str,delim, andcountparameters support implicit conversion between numeric and string types.
Examples
obclient> SELECT SUBSTRING_INDEX('ABCDABC', 'ABC', 0), SUBSTRING_INDEX('ABCDABC', 'ABC', 1), SUBSTRING_INDEX('ABCDABC', 'ABC', 2), SUBSTRING_INDEX('ABCDABC', 'ABC', 3), SUBSTRING_INDEX('ABCDABC', 'ABC', -1), SUBSTRING_INDEX('ABCDABC', 'ABC', -2), SUBSTRING_INDEX('ABCDABC', 'ABC', -3)\G
*************************** 1. row ***************************
SUBSTRING_INDEX('ABCDABC', 'ABC', 0):
SUBSTRING_INDEX('ABCDABC', 'ABC', 1):
SUBSTRING_INDEX('ABCDABC', 'ABC', 2): ABCD
SUBSTRING_INDEX('ABCDABC', 'ABC', 3): ABCDABC
SUBSTRING_INDEX('ABCDABC', 'ABC', -1):
SUBSTRING_INDEX('ABCDABC', 'ABC', -2): DABC
SUBSTRING_INDEX('ABCDABC', 'ABC', -3): ABCDABC
1 row in set