Tutorial Gateway

MySQL SUBSTRING_INDEX Function

by

The MySQL SUBSTRING_INDEX function is one of the String Function used to return a substring before the delimiter count occurs from a given string. Let me show you how to use this Substring_Index in MySQL with an example.

MySQL SUBSTRING_INDEX Function Syntax

The basic syntax of the SUBSTRING_INDEX string in MySQL is as shown below.

SUBSTRING_INDEX(String_Expression, Delimiter, Count)

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

MySQL SUBSTRING_INDEX Function Example 1

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 that .

Next, we used 2 as occurrence. It means it look 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);

OUTPUT

MySQL SUBSTRING_INDEX Function 1

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

First, we used -1. It means the 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);

OUTPUT

MySQL SUBSTRING_INDEX Function 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);

OUTPUT

MySQL SUBSTRING_INDEX Function 3

MySQL 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 `MySQL Tutorial`.`mathemetical functions`;

OUTPUT

MySQL SUBSTRING_INDEX Function 4