How SUBSTRING_INDEX() Works in MariaDB

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

SUBSTRING_INDEX() requires at three arguments; the string, the delimiter, and the count (i.e. number of occurrences of that delimiter).

Syntax

The syntax goes like this:

SUBSTRING_INDEX(str,delim,count)

Where str is the string, delim is the delimiter, and count is the number of occurrences of that delimiter to use for determining the substring to return.

If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned.

Example

Here’s a basic example:

SELECT SUBSTRING_INDEX('/users/homer/docs/cat_vids', '/', 3);

Result:

+-------------------------------------------------------+
| SUBSTRING_INDEX('/users/homer/docs/cat_vids', '/', 3) |
+-------------------------------------------------------+
| /users/homer                                          |
+-------------------------------------------------------+

Negative Count

Specifying a negative value for the count causes it to be counted backwards from the end of the string:

SELECT SUBSTRING_INDEX('/users/homer/docs/cat_vids', '/', -3);

Result:

+--------------------------------------------------------+
| SUBSTRING_INDEX('/users/homer/docs/cat_vids', '/', -3) |
+--------------------------------------------------------+
| homer/docs/cat_vids                                    |
+--------------------------------------------------------+

Delimiter Not Found

If the delimiter doesn’t exist in the string, the whole string is returned:

SELECT SUBSTRING_INDEX('/users/homer/docs/cat_vids', '.', 3);

Result:

+-------------------------------------------------------+
| SUBSTRING_INDEX('/users/homer/docs/cat_vids', '.', 3) |
+-------------------------------------------------------+
| /users/homer/docs/cat_vids                            |
+-------------------------------------------------------+

Null Arguments

If any (or all) of the arguments are null, the SUBSTRING_INDEX() function returns null:

SELECT 
    SUBSTRING_INDEX(null, '/', 3) AS "1",
    SUBSTRING_INDEX('/users/homer/docs/cat_vids', null, 3) AS "2",
    SUBSTRING_INDEX('/users/homer/docs/cat_vids', '/', null) AS "3",
    SUBSTRING_INDEX(null, null, null) AS "4";

Result:

+------+------+------+------+
| 1    | 2    | 3    | 4    |
+------+------+------+------+
| NULL | NULL | NULL | NULL |
+------+------+------+------+

Missing Arguments

Calling SUBSTRING_INDEX() without passing any arguments results in an error:

SELECT SUBSTRING_INDEX();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'SUBSTRING_INDEX'