MySQL SUBSTRING_INDEX Function

The MySQL SUBSTRING_INDEX is one of the String functions used to return a substring before the delimiter count occurs from a given string. Let me show you how to use this Substring_Index with an example. The basic syntax of the string SUBSTRING_INDEX is as shown below.

SUBSTRING_INDEX(String_Expression, Delimiter, Count)

The MySQL SUBSTRING_INDEX function counts the Delimiter occurrence. Once it finds it, it returns the substring from the start position to the position before that delimiter.

MySQL SUBSTRING_INDEX Function Example

The following query shows multiple ways to use this Substring_Index function. In the below statement, We used . Delimiter and Occurrence are 1. Once it finds the first occurrence of . it returns the substring up to .

Next, we used 2 as an occurrence. It means it looks for the second occurrence of . and returns the substring up to that . position.

SELECT SUBSTRING_INDEX('www.tutorialgateway.org', '.', 1);

SELECT SUBSTRING_INDEX('www.tutorialgateway.org', '.', 2);

SELECT SUBSTRING_INDEX('www.tutorial.gateway.org', '.', 2);
SUBSTRING_INDEX Example 1

In this String Function example, we use negative values as the count argument. It means this MySQL function counts the delimiter occurrence from right to left.

First, we used -1. It means the MySQL SUBSTRING_INDEX function starts looking from g, and once it finds the first occurrence (after org), it starts writing the substring.

SELECT SUBSTRING_INDEX('www.tutorial.gateway.org', '.', -1);

SELECT SUBSTRING_INDEX('www.tutorial.gateway.org', '.', -2);

SELECT SUBSTRING_INDEX('www.tutorial.gateway.org', '.', -3);
SUBSTRING INDEX Example 2

Here, we used the empty space as a delimiter.

SELECT SUBSTRING_INDEX('Hi MySQL tutorial gateway', ' ', 2);

SELECT SUBSTRING_INDEX('Hi MySQL tutorial gateway', ' ', 3);

SELECT SUBSTRING_INDEX('Hi MySQL tutorial gateway', ' ', -3);
Example 3

SUBSTRING_INDEX Example 2

The MySQL String SUBSTRING_INDEX function also allows you to work on column values. In this example, We are going to use this substring index function on English product Name.

SELECT EnglishProductName, 
       SUBSTRING_INDEX(EnglishProductName, ' ', 2) AS 'ENG',
       SUBSTRING_INDEX(EnglishProductName, '-', 1) AS 'ENG 2',
       Color,
       StandardCost,
       SalesAmount,
       TaxAmt
  FROM `mathemetical functions`;
MySQL SUBSTRING_INDEX Function 4

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.