SUBSTRING_INDEX() FUNCTION
The MySQL SUBSTRING_INDEX function is used to get the substring of string before several occurrences of delimiter. The various versions of MySQL support the SUBSTRING_INDEX function, namely, MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0 and MySQL 3.23.
Syntax:
SUBSTRING_INDEX( string, delimiter, number )
Parameters:
string: It is used to specify the string to evaluate.
delimiter: It is used to specify the delimiter to search for.
number: It is used to specify the number of times to search.
Example 1:
mysql> SELECT SUBSTRING_INDEX ( ‘abc_xyz_123’, ‘_’, 1 );
Output:
‘abc’
Explanation:
The substring of string before the first occurrence of delimiter is returned.
Example 2:
mysql> SELECT SUBSTRING_INDEX ( ‘abc_xyz_123’, ‘_’, 2 );
Output:
‘abc_xyz’
Explanation:
The substring of string before the second occurrence of delimiter is returned.
Example 3:
mysql> SELECT SUBSTRING_INDEX ( ‘abc_xyz_123’, ‘_’, -1 );
Output:
‘123’
Explanation:
The substring of string before the first occurrence of delimiter from the end-side is returned.
Example 4:
mysql> SELECT SUBSTRING_INDEX ( ‘abc_xyz_123’, ‘_’, -2 );
Output:
‘xyz_123’
Explanation:
The substring of string before the second occurrence of delimiter from the end-side is returned.