SUBSTRING_INDEX

Declaration

SUBSTRING_INDEX(str, delim, count)

Description

Returns the substring from the beginning of the string str up to and excluding the countth occurrence of the delimiter delim.

• If count is positive, the substring from the beginning of the string str up to and excluding the countth occurrence of the delimiter delim is returned.

• If count is negative, the substring from the countth occurrence of the delimiter delim from the end of the string str is returned.

• If any parameter is NULL, NULL is returned.

• If str or delim is an empty string, an empty string is returned.

• If count=0, an empty string is returned.

• The parameters str, delim, and count support implicit conversion between numeric and string types.

Examples

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.001 sec)