MySQL SUBSTRING_INDEX() Function

The MySQL SUBSTRING_INDEX() function accepts a string value, a delimiter, and a numerical value representing the number of occurrences of the delimiter (say N) as parameters. It returns the substring starting from the left end of the given string up to the N^th occurrence of the specified delimiter.

If you pass a negative value as the third argument (N), the resulting substring will start after the specified (N) number of occurrences of the delimiter starting from the right end of the given string.

Syntax

Following is the syntax of MySQL SUBSTRING_INDEX() function −

SUBSTRING_INDEX(str,delim,N)

Parameters

This function takes a string value, a delimiter, and a count as parameter.

Return Value

This function returns a substring from the input string before the specified count of occurrences of the delimiter.

Example

In the following example, we are extracting a substring up to the 3^rd occurrence of a delimiter '' −

SELECT SUBSTRING_INDEX('Hello how are you welcome to Tutorialspoint', ' ', 3);

Following is the output of the above code −

Example

You can also pass a negative value as the third argument (N) of this function. If you do so, the resultant substring will start after the specified (N) number of occurrences of the given delimiter starting from the right end of the given string −

SELECT SUBSTRING_INDEX('Hello how are you welcome to Tutorialspoint', ' ', -3);

The output obtained is as follows −

Example

You can also pass numerical value, in the form of strings as the first argument of this function −

SELECT SUBSTRING_INDEX('6486582-654045-7493-7830-3693_4333', '-', 3);

The result produced is as follows −

Example

If any of the given arguments is NULL, the function returns NULL −

SELECT SUBSTRING_INDEX(NULL, '.', -2);

The result produced is as shown below −

Example

If the argument passed as the value for the third (N) parameter is 0, the function returns an empty string −

SELECT SUBSTRING_INDEX('Hello how are you welcome to Tutorialspoint', ' ', 0);

Following is the output of the above code −

Example

Let us create a table named "DISPATCHES" and insert records into it using CREATE and INSERT statements as shown below −

CREATE TABLE DISPATCHES(
   ProductName VARCHAR(255), 
   CustomerName VARCHAR(255), 
   Price INT, 
   Location VARCHAR(255),
   DispatchTimeStamp timestamp
);

Now, let us insert records into it using the INSERT statement −

INSERT INTO DISPATCHES VALUES 
('Key-Board', 'Raja', 7000, 'Hyderabad', TIMESTAMP('2019-05-04 15-02-45')),
('Earphones', 'Roja', 2000, 'Vishakhapatnam', TIMESTAMP('2019-06-26 14-13-12')),
('Mouse', 'Puja', 3000, 'Vijayawada', TIMESTAMP('2019-12-07 07-50-37')),
('Mobile', 'Vanaja', 9000, 'Chennai', TIMESTAMP('2018-03-21 16-00-45')),
('Headset', 'Jalaja', 6000, 'Goa', TIMESTAMP('2018-12-30 10-49-27')),
('Watch', 'Rajan', 4000, 'Chennai', TIMESTAMP('2019-04-21 14-17-02'));

The DISPATCHES table obtained is as follows −

Following query extracts the substring from the column "DispatchTimeStamp" up to the 2^nd occurrence of '-' −

SELECT ProductName, Price, DispatchTimeStamp, SUBSTRING_INDEX(DispatchTimeStamp, '-', 2) as Result 
FROM DISPATCHES;

Output

After executing the above code, we get the following output −

Example

The following query extracts the substring from "DispatchTimeStamp" after the last occurrence of '-' −

SELECT ProductName, Price, DispatchTimeStamp, SUBSTRING_INDEX(DispatchTimeStamp, '-', -1) as Result 
FROM DISPATCHES;

Output

After executing the above code, we get the following output −