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 SUBSTRING_INDEX 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 that .
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);
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 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);
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);
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 `mathemetical functions`;
