SQL Tutorial SQL Advanced SQL Database SQL References

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:

OrderQuantityPriceIP_Address
1001.5876.240.249.145
1201.61127.255.255.255
1251.78191.70.78.255
501.80223.241.209.223
2001.72129.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.

IP Address BreakDown

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:

OrderQuantityPriceIP_AddressNetwork_PartHost_Part
1001.5876.240.249.14576.240249.145
1201.61127.255.255.255127.255255.255
1251.78191.70.78.255191.7078.255
501.80223.241.209.223223.241209.223
2001.72129.144.50.56129.14450.56

❮ MySQL Functions