Declaration
SUBSTRING_INDEX(str, delim, count)
Description
Return the substring from str up to, but not including, the first occurrence of the delimiter delim. The function also returns the number of characters matched.
If
countis positive, return all content to the left of the final delimiter (starting from the left).If
countis negative, return all characters to the right of the delimiter (starting from the right).If any parameter is
NULL, returnNULL;If
strordelimis an empty string, an empty string is returned.If
count=0, an empty string is also returned.The
str,delim, andcountparameters support implicit conversion between numeric and string values.
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