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 function with an example, and the basic syntax of the string SUBSTRING_INDEX is as shown below.

SUBSTRING_INDEX(String_Expression, Delimiter, Count)

The 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) a;

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

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

+------+---------------------+--------------+
| a    | b                   | c            |
+------+---------------------+--------------+
| www  | www.tutorialgateway | www.tutorial |
+------+---------------------+--------------+

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) d;

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

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

+------+-------------+----------------------+
| d    | e           | f                    |
+------+-------------+----------------------+
| org  | gateway.org | tutorial.gateway.org |
+------+-------------+----------------------+

Here, we used the empty space as a delimiter.

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

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

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

+----------+-------------------+------------------------+
| g        | h                 | i                      |
+----------+-------------------+------------------------+
| Hi MySQL | Hi MySQL tutorial | MySQL tutorial gateway |
+----------+-------------------+------------------------+

SUBSTRING_INDEX Example 2

This String function also allows you to work on column values. In this example, we are going to use this substring index function on the 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`;