In MariaDB, SUBSTR() is a synonym for SUBSTRING().

It’s a built-in string function that returns a substring from a given string.

It 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:

SUBSTR(str,pos), 
SUBSTR(str FROM pos), 
SUBSTR(str,pos,len),
SUBSTR(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 SUBSTR('Good doggy', 6);

Result:

+-------------------------+
| SUBSTR('Good doggy', 6) |
+-------------------------+
| doggy                   |
+-------------------------+

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

SELECT SUBSTR('Good doggy' FROM 6);

Result:

+-----------------------------+
| SUBSTR('Good doggy' FROM 6) |
+-----------------------------+
| doggy                       |
+-----------------------------+

Substring Length

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

SELECT SUBSTR('Good doggy', 6, 3);

Result:

+----------------------------+
| SUBSTR('Good doggy', 6, 3) |
+----------------------------+
| dog                        |
+----------------------------+

And here it is using standard SQL syntax:

SELECT SUBSTR('Good doggy' FROM 6 FOR 3);

Result:

+-----------------------------------+
| SUBSTR('Good doggy' FROM 6 FOR 3) |
+-----------------------------------+
| dog                               |
+-----------------------------------+

Negative Position

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

SELECT SUBSTR('Good doggy', -5);

Result:

+--------------------------+
| SUBSTR('Good doggy', -5) |
+--------------------------+
| doggy                    |
+--------------------------+

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

SELECT SUBSTR('Good doggy' FROM -5 FOR 3);

Result:

+------------------------------------+
| SUBSTR('Good doggy' FROM -5 FOR 3) |
+------------------------------------+
| dog                                |
+------------------------------------+

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 
    SUBSTR('Good doggy', 0) AS "0",
    SUBSTR('Good doggy', 1) AS "1";

Result:

+------------+------------+
| 0          | 1          |
+------------+------------+
| Good doggy | Good doggy |
+------------+------------+

Here it is in default mode:

SET SQL_MODE=DEFAULT;
SELECT 
    SUBSTR('Good doggy', 0) AS "0",
    SUBSTR('Good doggy', 1) AS "1";

Result:

+------+------------+
| 0    | 1          |
+------+------------+
|      | Good doggy |
+------+------------+

Null Arguments

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

SELECT 
    SUBSTR(null, 3, 3),
    SUBSTR('Doggy', null, 3),
    SUBSTR('Doggy', 3, null),
    SUBSTR(null, null, null);

Result:

+--------------------+--------------------------+--------------------------+--------------------------+
| SUBSTR(null, 3, 3) | SUBSTR('Doggy', null, 3) | SUBSTR('Doggy', 3, null) | SUBSTR(null, null, null) |
+--------------------+--------------------------+--------------------------+--------------------------+
| NULL               | NULL                     | NULL                     | NULL                     |
+--------------------+--------------------------+--------------------------+--------------------------+

Missing Arguments

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

SELECT SUBSTR();

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