MySQL SUBSTRING_INDEX() – Working with Substrings in MySQL

By /
SUBSTRING INDEX Function

In this tutorial, we will learn about the MySQL SUBSTRING_INDEX() function. SUBSTRING_INDEX() is a string function that is used to return the substring of a string before a specific number of occurrences of a given delimiter.

For instance, you have the following string value – “192.168.1.10”.

Now if you specify the delimiter as ‘.’ and its number of occurrences and 2 then the SUBSTRING_INDEX() function will return “192.168” as our result.

So we are extracting the substring from the string until the second occurrence of the ‘.’ delimiter. SUBSTRING_INDEX() is usually used with the SELECT statement. 

Syntax of MySQL SUBSTRING_INDEX()

SUBSTRING_INDEX(string, delimiter, number);Code language: SQL (Structured Query Language) (sql)

Where:

  • ‘string’ is the string from which you wish to extract the substring,
  • ‘delimiter’ is the delimiter to search for and,
  • ‘number’ is the number of occurrences of the delimiter.

Note:

  1. All the above parameters are required parameters.
  2. The ‘number’ parameter can have positive and negative integers as values. If the value is a positive integer, then the number of delimiter occurrences are searched from the left side. If the value is a negative integer, then the number of delimiter occurrences are searched from the right side.

Examples of MySQL SUBSTRING_INDEX()

Let us look at a few basic queries demonstrating the MySQL SUBSTRING_INDEX() function. We will use aliases in the queries to make our output readable. Consider the below queries:

SELECT SUBSTRING_INDEX(‘support@helpdesk.com’, ‘@’, 1) AS Result; 
SELECT SUBSTRING_INDEX(‘support@helpdesk.com’, ‘@’, 2) AS Result; 
SELECT SUBSTRING_INDEX(‘www.yahoo.co.in’, ‘.’, 3) AS Result;Code language: SQL (Structured Query Language) (sql)
  • In the first query, we want the substring to be till the first occurrence of the delimiter ‘@’.
  • In the second query, we want the substring to be till the second occurrence of the delimiter ‘@’.
  • And, in the third query, we want the substring to be till the third occurrence of the delimiter ‘.’.

The output is as follows,

Substring Index Basic Example

MySQL SUBSTRING_INDEX() With Negative Values for Number of Delimiters

As I mentioned earlier while discussing the syntax, we can use negative integers as the value in the ‘number’ parameter. A negative integer indicates counting the number of occurrences of the delimiter from the right side. Let us see a couple of examples of this.

Consider the below queries.

SELECT SUBSTRING_INDEX('www.yahoo.co.in', '.', -1) AS Result; 
SELECT SUBSTRING_INDEX('www.yahoo.co.in', '.', -3) AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is,

Substring Index Negative Values

Working with Table Data

Let us look at a few examples of tables. Consider the below Employee table.

Employee Table Substring Index
Employee Table

Simple Example With Table Data

How about finding the joining year of every employee from the Date_Joined column using MySQL SUBSTRING_INDEX()?

SELECT eid, Name, SUBSTRING_INDEX(Date_Joined, ‘-’, 1) AS JoiningYear FROM Employee;Code language: SQL (Structured Query Language) (sql)

And the output is,

Substring Index Table Example 1

Nested SUBSTRING_INDEX() Functions

Now, let us find the joining month of every employee from the Date_Joined column using SUBSTRING_INDEX().

SELECT eid, Name, SUBSTRING_INDEX(SUBSTRING_INDEX(Date_Joined, ‘-’, 2), ‘-’, -1) AS JoiningMonth FROM Employee;Code language: SQL (Structured Query Language) (sql)

The inner MySQL SUBSTRING_INDEX() function will extract the year and month values. The outer SUBSTRING_INDEX() function has a negative one value as the ‘number’ parameter. Hence, it will extract the substring from the right side till the first occurrence of ‘-’ delimiter.

And our output is,

Substring Index Table Example 2

Nested SUBSTRING_INDEX() Functions with different delimiters

Now how about using a similar logic and displaying which email service every employee uses? Note that only the email service website name should be displayed, the ‘.com’ part should not be in the result. So, our query is,

SELECT eid, Name, SUBSTRING_INDEX(SUBSTRING_INDEX(Email, ‘@’, -1), ‘.’, 1) AS EmailServiceUsed FROM Employee;Code language: SQL (Structured Query Language) (sql)

The inner SUBSTRING_INDEX() function will extract the domain part from the email address. This part will contain the website name as well as the ‘.com’ part. But since we do not want the ‘.com’ part in our result-set, we use the outer SUBSTRING_INDEX() function to get the website name.

Our output is,

Substring Index Table Example 3

Conclusion

SUBSTRING_INDEX() function is a very powerful function in MySQL and one you would find using often. I would highly recommend that you practice more examples of it by playing around with it!

References