SUBSTRING_INDEX() function in MySQL
Last Updated :
23 Sep, 2020
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 |
Share your thoughts in the comments
Please Login to comment...