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:
|
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