MySQL SUBSTRING_INDEX

In this guide, we will understand the concept of the substring_index function in MySQL. Such a concept allows a user to fetch the string of his choice from an array of different strings.

Let’s dive in and understand this function.

SUBSTRING_INDEX in MySQL

First, let’s understand a couple of important concepts essential for working the substring_index function. These are namely: DELIMITER and CHARINDEX().

Delimiter is a compound symbol that serves as the main point where we want the string to break. The string will be broken at that point, and the string fetched will be either from ahead of that point or before, depending on our input.

Charindex() is a function in MySQL that is used when we want to fetch the position of a specific substring in a string and return its position. Its parameters include the (substring, string).

Substring Function Code

The following code showcases the working of the Substring_index function. This code is the SQL Server equivalent of MySQL’s substring_index function.

The following code should be run on the SQL Server.

CREATE FUNCTION SUB_STRING
(
@STRING VARCHAR(8000),
@DELIMITER VARCHAR(1),
@POSITION INT
)
RETURNS VARCHAR(8000)
AS
BEGIN
IF @POSITION = 0
BEGIN
	RETURN SUBSTRING(@STRING,0,CHARINDEX(@DELIMITER,@STRING))
END
IF @POSITION = 1
BEGIN
	RETURN SUBSTRING(@STRING,CHARINDEX(@DELIMITER,@STRING)+1,LEN(@STRING))
END
RETURN @STRING
END;

Let’s understand the code.

We have created a function, namely SUB_STRING and inside this function has parameters @string (string from which substring will be fetched), @delimiter and @position (it has two values 0 or 1).

@Position is such a parameter which, if set to 0, would start fetching from starting index of the string up to the delimiter point and, if set to 1, will start fetching from the 1 point ahead of delimiter point and up to the last of string.

The Begin clause indicates the start of the code block, and the End clause indicates the end of the code block.

In the first IF condition, the position is set to 0. In the substring function, we got the string to be fetched from the 0 index, the Charindex() function.

This would return the substring from the 0 index to the delimiter point.

Example 1

Understand this by the following example.

SELECT dbo.SUB_STRING('ahsanali@email.com','@',0)

As you can see, it returned the substring from the 0 index and up to the delimiter point.

The second IF condition sets the position to 1. In the substring function, we got the Charindex() function with a +1 condition that would fetch the substring, which is a 1 index ahead of the delimiter point and up to the last of string.

Example 2

Let’s understand further with the help of another example.

SELECT dbo.SUB_STRING('ahsanali@email.com','@',1)

As you can see, it returned the substring that was the 1 index ahead of the delimiter point and up to the last string.

Also, another point is that if you give the wrong value at @position (0 or 1), it would return the complete string.

SELECT dbo.SUB_STRING('ahsanali@email.com','&',3)