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.

In this article, we will 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 will return the substring from start position to the position before that delimiter.

MySQL SUBSTRING_INDEX Function Example 1

The following query will show multiple ways to use this Substring_Index function.

In below statement, We used . Delimiter, and Occurrence is 1 . Once it finds the first occurrence of . then it will return the substring upto that .

Next, we used 2 as occurrence. It means, it will look for second occurrence of a . and returns the substring upto 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 will use negative values as the count argument. It means, function will count the delimiter occurrence from right to left.

First, we used -1. It means, function starts looking from g and once it finds the first occurrence (after org), it will start 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

Thank You for Visiting Our Blog