MySQL SUBSTRING_INDEX() Function

The MySQL SUBSTRING_INDEX() function returns the substring of string before number of occurrences of delimiter.

Syntax

SUBSTRING_INDEX(string, delimiter, number)

Parameters

`string`	`Required.` Specify the source string.
`delimiter`	`Required.` Specify the delimiter to search for in string.
`number`	`Required.` Specify the number of times to search for the delimiter. It can be a positive or negative number: If it is a positive value, everything from the left of the targeted delimiter is returned by this function. If it is a negative value, everything from the right of the targeted delimiter is returned by this function.

Return Value

Returns the substring of string before number of occurrences of delimiter.

Example 1:

The example below shows the usage of SUBSTRING_INDEX() function.

mysql> SELECT SUBSTRING_INDEX('www.alphacodingskills.com', '.', 0);
Result: NULL

mysql> SELECT SUBSTRING_INDEX('www.alphacodingskills.com', '.', 1);
Result: 'www'

mysql> SELECT SUBSTRING_INDEX('www.alphacodingskills.com', '.', 2);
Result: 'www.alphacodingskills'

mysql> SELECT SUBSTRING_INDEX('www.alphacodingskills.com', '.', 3);
Result: 'www.alphacodingskills.com'

mysql> SELECT SUBSTRING_INDEX('www.alphacodingskills.com', '.', 4);
Result: 'www.alphacodingskills.com'

mysql> SELECT SUBSTRING_INDEX('www.alphacodingskills.com', '.', -1);
Result: 'com'

mysql> SELECT SUBSTRING_INDEX('www.alphacodingskills.com', '.', -2);
Result: 'alphacodingskills.com'

mysql> SELECT SUBSTRING_INDEX('www.alphacodingskills.com', '.', -3);
Result: 'www.alphacodingskills.com'

mysql> SELECT SUBSTRING_INDEX('www.alphacodingskills.com', '.', -4);
Result: 'www.alphacodingskills.com'

Example 2:

Consider a database table called Orders with the following records:

OrderQuantity	Price	IP_Address
100	1.58	76.240.249.145
120	1.61	127.255.255.255
125	1.78	191.70.78.255
50	1.80	223.241.209.223
200	1.72	129.144.50.56

In the query below, the SUBSTRING_INDEX() function is used to extract the network part and host part of the IP address mentioned in column IP_Address.

SELECT *, 
SUBSTRING_INDEX(IP_Address, '.', 2) AS Network_Part,
SUBSTRING_INDEX(IP_Address, '.', -2) AS Host_Part
FROM Employee;

This will produce the result as shown below:

OrderQuantity	Price	IP_Address	Network_Part	Host_Part
100	1.58	76.240.249.145	76.240	249.145
120	1.61	127.255.255.255	127.255	255.255
125	1.78	191.70.78.255	191.70	78.255
50	1.80	223.241.209.223	223.241	209.223
200	1.72	129.144.50.56	129.144	50.56

❮ MySQL Functions