SUBSTRING_INDEX() function in MySQL is used to return a substring from a string before a specified number of occurrences of the delimiter.
Syntax :
SUBSTRING_INDEX( str, delim, count )
Parameter : This method accepts three-parameter as mentioned above and described below :
- str : The original string from which we want to create a substring.
- delim : Is a string that acts as a delimiter. The function performs a case-sensitive match when searching for the delimiter.
- count : It identifies the number of times to search for the delimiter. It can be both a positive or negative number. If it is a positive number, this function returns all to the left of the delimiter. If it is a negative number, this function returns all to the right of the delimiter.
Returns : It returns substring from a given string.
Example-1 : SUBSTRING_INDEX() function with a positive number of occurrences of a delimiter
SELECT SUBSTRING_INDEX("www.geeksforgeeks.org", ".", 2) as Sub_Str;
Output :
| Sub_Str |
|---|
| www.geeksforgeeks |
Example-2 : SUBSTRING_INDEX() function with a negative number of occurrences of a delimiter.
SELECT SUBSTRING_INDEX("www.geeksforgeeks.org", ".", -2) as Sub_Str;
Output :
| Sub_Str |
|---|
| geeksforgeeks.org |
Example-3 : SUBSTRING_INDEX() function with the table data.
Table : Employee :
| Employee_Id | Address |
|---|---|
| 101 | 700000 Kolkata W.B |
| 102 | 735102 Jalpaiguri W.B |
| 103 | 721101 Midnapore W.B |
| 104 | 734001 Siliguri W.B |
Now, we will find the pin number address of every employee by using SUBSTRING_INDEX function.
SELECT SUBSTRING_INDEX(Address, ' ', 1 ) AS Pin_Num FROM Employee
Output :
| Pin_Num |
|---|
| 700000 |
| 735102 |
| 721101 |
| 734001 |