MariaDB SUBSTRING_INDEX() Function

In MariaDB, SUBSTRING_INDEX() is a built-in string function that returns the substring of the specified number of occurrences of the specified delimiter.

MariaDB SUBSTRING_INDEX() Syntax

Here is the syntax of the MariaDB SUBSTRING_INDEX() function:

SUBSTRING_INDEX(str, delim, count)

Parameters

str

Required. The original string.

delim

Required. The delimiter to search for in the original string.

count

Required. The number of occurrences of the separator. If count is negative, the substring after the delimiter is returned counting delimiters from the end of the string forward.

If you provide the wrong number of parameters, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'SUBSTRING_INDEX'.

Return value

The MariaDB SUBSTRING_INDEX() function returns a substring of a string in which the specified delimiter occurs the specified number of times.

If the argument count is equal 0, the SUBSTRING_INDEX() function returns an empty string '';

If the parameter count is negative, the delimiter is counted forward from the end of the string, and the substring after the delimiter is returned.

If the given delimiter is not found, the SUBSTRING_INDEX() function returns the original string.

If any argument is NULL, the SUBSTRING_INDEX() function will return NULL.

MariaDB SUBSTRING_INDEX() Examples

Basic example

This statement shows the basic usage of the MariaDB SUBSTRING_INDEX() function:

SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);

Output:

+------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', 2) |
+------------------------------------------+
| www.mysql                                |
+------------------------------------------+

Negative number

If the count argument is negative, SUBSTRING_INDEX() count the delimiter from the end of the string forward, and return the substring after the delimiter.

SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);

Output:

+-------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', -2) |
+-------------------------------------------+
| mysql.com                                 |
+-------------------------------------------+

A invalid separator

If the given delimiter is not found, the SUBSTRING_INDEX() function returns the original string.

SELECT SUBSTRING_INDEX('www.mysql.com', '/', 2);

Output:

+------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '/', 2) |
+------------------------------------------+
| www.mysql.com                            |
+------------------------------------------+

Conclusion

In MariaDB, SUBSTRING_INDEX() is a built-in string function that returns the substring of the specified number of occurrences of the specified delimiter.