MySQL SUBSTRING_INDEX Function | Substring By Delimiter

MySQL SUBSTRING_INDEX is an inbuilt Function in MySQL. It returns a substring before the number of occurrences of a delimiter. In this article, we will discuss the MySQL SUBSTRING_INDEX Function. Also, we will discuss a few examples of using it.

Moreover, you can also go through MySQL Substring Function which returns a substring by specifying the start and end point.

Syntax

SUBSTRING_INDEX( string, delimiter, number )

Parameters

The MySQL SUBSTRING_INDEX expects three parameters. Moreover, all the parameters are mandatory. The description of the parameters is as follows:

• string: You need to pass the source string as the first parameter to the function.
• delimiter: The second parameter specifies the delimiter to search in the source string.
• number: The third parameter is the number of times to search for the delimiter. The number can take positive or negative values.
  • Positive: When number is positive, the function returns everything from the right of the delimiter.
  • Negative: When number is negative, the function returns everything from the left of the delimiter.

Return Value

The MySQL SUBSTRING_INDEX Function returns a substring of the input string before a specified number of delimiters occur.

Also, this function is supported from MySQL Version 3.23.

Examples

Let’s discuss a few examples of using the function.

Example 1: Positive Number

mysql> SELECT SUBSTRING_INDEX('www.concatly.com', '.', 1) as substring;
+-----------+
| substring |
+-----------+
| www       |
+-----------+

mysql> SELECT SUBSTRING_INDEX('www.concatly.com', '.', 2) as substring;
+--------------+
| substring    |
+--------------+
| www.concatly |
+--------------+

In the above example, we pass ‘.’ as the delimiter. The function returns the substring from left of delimiter.

Example 2: Negative Number

Similarly, you can pass a negative number. The function returns a substring from the right of the delimiter.

mysql> SELECT SUBSTRING_INDEX('www.concatly.com', '.', -1) as substring;
+-----------+
| substring |
+-----------+
| com       |
+-----------+

mysql> SELECT SUBSTRING_INDEX('www.concatly.com', '.', -2) as substring;
+--------------+
| substring    |
+--------------+
| concatly.com |
+--------------+

Conclusion

In conclusion, we discussed the MySQL SUBSTRING_INDEX Function. You can learn more about it on MySQL Official Documentation. Also, you can learn about more MySQL String Functions on Concatly.