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 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 Function Code
The following code showcases the working of the
Substring_index function. This code is the SQL Server equivalent of MySQL’s
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),
@position (it has two values
@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.
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
This would return the substring from the
0 index to the delimiter point.
Understand this by the following example.
As you can see, it returned the substring from the
0 index and up to the delimiter point.
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.
Let’s understand further with the help of another example.
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.