Syntax
SUBSTRING_INDEX(str, delim, count)
Purpose
SUBSTRING_INDEX() returns a string from str before delim and count appear.
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