SUBSTRING_INDEX() FUNCTION in MySQL

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.