How SUBSTRING() Works in MariaDB

In MariaDB, SUBSTRING() is a built-in string function that returns a substring from a given string.

SUBSTRING() requires at least two arguments; the string, and the position for which to extract the substring from. It also accepts an optional third argument that allows you to specify how long the substring should be.

Syntax

The syntax takes the following forms:

SUBSTRING(str,pos), 
SUBSTRING(str FROM pos), 
SUBSTRING(str,pos,len),
SUBSTRING(str FROM pos FOR len)

Where str is the string, pos is the starting position of the substring, and len is the number of characters to extract.

The two forms that use the FROM keyword are standard SQL syntax.

Example

Here’s a basic example:

SELECT SUBSTRING('Big fat cat', 5);

Result:

+-----------------------------+
| SUBSTRING('Big fat cat', 5) |
+-----------------------------+
| fat cat                     |
+-----------------------------+

Here’s the same example, but using the standard SQL syntax:

SELECT SUBSTRING('Big fat cat' FROM 5);

Result:

+---------------------------------+
| SUBSTRING('Big fat cat' FROM 5) |
+---------------------------------+
| fat cat                         |
+---------------------------------+

Substring Length

Here’s an example that specifies the length of the substring to extract:

SELECT SUBSTRING('Big fat cat', 5, 3);

Result:

+--------------------------------+
| SUBSTRING('Big fat cat', 5, 3) |
+--------------------------------+
| fat                            |
+--------------------------------+

And here it is using standard SQL syntax:

SELECT SUBSTRING('Big fat cat' FROM 5 FOR 3);

Result:

+---------------------------------------+
| SUBSTRING('Big fat cat' FROM 5 FOR 3) |
+---------------------------------------+
| fat                                   |
+---------------------------------------+

Negative Position

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

SELECT SUBSTRING('Big fat cat', -3);

Result:

+------------------------------+
| SUBSTRING('Big fat cat', -3) |
+------------------------------+
| cat                          |
+------------------------------+

A negative position can also be used when using the standard SQL syntax:

SELECT SUBSTRING('Big fat cat' FROM -7 FOR 3);

Result:

+----------------------------------------+
| SUBSTRING('Big fat cat' FROM -7 FOR 3) |
+----------------------------------------+
| fat                                    |
+----------------------------------------+

In this case I also set a length for the substring.

Oracle Mode

When in Oracle mode, a start position of 0 (zero) is treated as 1. However, a start position of 1 is also treated as 1.

This is in contrast to other modes, where 0 will return an empty string.

Example:

SET SQL_MODE=ORACLE;
SELECT 
    SUBSTRING('Big fat cat', 0) AS "0",
    SUBSTRING('Big fat cat', 1) AS "1";

Result:

+-------------+-------------+
| 0           | 1           |
+-------------+-------------+
| Big fat cat | Big fat cat |
+-------------+-------------+

Here it is in default mode:

SET SQL_MODE=DEFAULT;
SELECT 
    SUBSTRING('Big fat cat', 0) AS "0",
    SUBSTRING('Big fat cat', 1) AS "1";

Result:

+------+-------------+
| 0    | 1           |
+------+-------------+
|      | Big fat cat |
+------+-------------+

Null Arguments

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

SELECT 
    SUBSTRING(null, 3, 3),
    SUBSTRING('Coffee', null, 3),
    SUBSTRING('Coffee', 3, null),
    SUBSTRING(null, null, null);

Result:

+-----------------------+------------------------------+------------------------------+-----------------------------+
| SUBSTRING(null, 3, 3) | SUBSTRING('Coffee', null, 3) | SUBSTRING('Coffee', 3, null) | SUBSTRING(null, null, null) |
+-----------------------+------------------------------+------------------------------+-----------------------------+
| NULL                  | NULL                         | NULL                         | NULL                        |
+-----------------------+------------------------------+------------------------------+-----------------------------+

Missing Arguments

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

SELECT SUBSTRING();

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1