Syntax
SUBSTRING_INDEX(str, delim, count)
Purpose
You can call this function to return a substring from the str string after delim occurs count times.
If
countis a positive value, the content to the left of the final delimiter (starting from the left) is returned.If
countis a negative value, the content to the right of the delimiter (starting from the right) is returned.If any argument is
NULL, the return value isNULL.If
strordelimis an empty string, an empty string is returned.If
count=0, an empty string is returned.
str, delim, and count support implicit conversion between numeric values and strings.
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 (0.00 sec)