SUBSTRING_INDEX() function in MySQL

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