JIYIK CN >

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

Let's take a closer look at this function.

SUBSTRING_INDEX in MySQL

First, let's understand a few substring_indeximportant concepts that are essential to using the function. They are: DELIMITERand CHARINDEX().

The delimiter is a composite symbol that serves as the main point where we want the string to break. The string will break at that point and the extracted string will be the string before or after that point, depending on our input.

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

substring function code

The following code shows how the Substring_index function works. This code is equivalent to the SQL Server substring_index function of MySQL.

The following code should be run on 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 the function we have parameters @string (the string from which the substring will be taken), @delimiter and @position (which has two values ​​0 or 1).

@PositionThis is a parameter. If it is set to 0, it will get from the starting index of the string to the separation point. If it is set to 1, it will get from 1 point before the separation point until the last string.

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

In the first IF condition, position is set to 0. In the substring function, we get the string to be extracted from the 0 index, i.e., Charindex()function.

This returns the substring from the 0 index to the delimiting point.

Example 1

Let us understand this with the following example.

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

As you can see, it returns the substring from index 0 to the delimiter point.

The second IF condition sets the position to 1. In the substring function, we have the Charindex() function with the +1 condition which will get the substring which is the 1 index before the delimiter point till the last .

Example 2

Let us understand further with the help of another example.

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

As you can see, the substring it returns is the 1 index before the delimiting point and goes all the way to the end of the string.

Also, one more thing is that if you @position(0 or 1)give a wrong value at , it will return the complete string.

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