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 Nth 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 3rd occurrence of a delimiter '' −

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

Following is the output of the above code −

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

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 −

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

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 −

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

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 −

SUBSTRING_INDEX(NULL, '.', -2)
0x

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 −

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

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 −

ProductName CustomerName Price Location DispatchTimeStamp
Key-Board Raja 7000 Hyderabad 2019-05-04 15:02:45
Earphones Roja 2000 Vishakhapatnam 2019-06-26 14:13:12
Mouse Puja 3000 Vijayawada 2019-12-07 07:50:37
Mobile Vanaja 9000 Chennai 2018-03-21 16:00:45
Headset Jalaja 6000 Goa 2018-12-30 10:49:27
Watch Rajan 4000 Chennai 2019-04-21 14:17:02

Following query extracts the substring from the column "DispatchTimeStamp" up to the 2nd 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 −

ProductName Price DispatchTimeStamp Result
Key-Board 7000 2019-05-04 15:02:45 2019-05
Earphones 2000 2019-06-26 14:13:12 2019-06
Mouse 3000 2019-12-07 07:50:37 2019-12
Mobile 9000 2018-03-21 16:00:45 2018-03
Headset 6000 2018-12-30 10:49:27 2018-12
Watch 4000 2019-04-21 14:17:02 2019-04

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 −

ProductName Price DispatchTimeStamp Result
Key-Board 7000 2019-05-04 15:02:45 04 15:02:45
Earphones 2000 2019-06-26 14:13:12 26 14:13:12
Mouse 3000 2019-12-07 07:50:37 07 07:50:37
Mobile 9000 2018-03-21 16:00:45 21 16:00:45
Headset 6000 2018-12-30 10:49:27 30 10:49:27
Watch 4000 2019-04-21 14:17:02 21 14:17:02
mysql-substring-index-function.htm
Advertisements