In MariaDB, MID() is a synonym for SUBSTRING(). It returns a substring from a given string.

According to the MariaDB documentation it’s a synonym for the SUBSTRING(str,pos,len) syntax of SUBSTRING(), however, my tests show that it works with all of the various forms.

Syntax

According to the MariaDB documentation, the syntax goes like this:

MID(str,pos,len)

However, I’ve found that it works with the following forms (which are the same forms that SUBSTRING() supports):

MID(str,pos), 
MID(str FROM pos), 
MID(str,pos,len),
MID(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.

Example

Here’s a basic example:

SELECT MID('Drink your beer', 7);

Result:

+---------------------------+
| MID('Drink your beer', 7) |
+---------------------------+
| your beer                 |
+---------------------------+

Here’s the same example, but using the FROM syntax:

SELECT MID('Drink your beer' FROM 7);

Result:

+-------------------------------+
| MID('Drink your beer' FROM 7) |
+-------------------------------+
| your beer                     |
+-------------------------------+

Substring Length

Here’s an example that specifies the length of the substring to extract. This is the only syntax that the MariaDB documentation cites for the MID() function.

SELECT MID('Drink your beer', 7, 4);

Result:

+------------------------------+
| MID('Drink your beer', 7, 4) |
+------------------------------+
| your                         |
+------------------------------+

And here it is using FROM...FOR syntax:

SELECT MID('Drink your beer' FROM 7 FOR 4);

Result:

+-------------------------------------+
| MID('Drink your beer' FROM 7 FOR 4) |
+-------------------------------------+
| your                                |
+-------------------------------------+

Negative Position

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

SELECT MID('Drink your beer', -9);

Result:

+----------------------------+
| MID('Drink your beer', -9) |
+----------------------------+
| your beer                  |
+----------------------------+

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

SELECT MID('Drink your beer' FROM -9 FOR 4);

Result:

+--------------------------------------+
| MID('Drink your beer' FROM -9 FOR 4) |
+--------------------------------------+
| your                                 |
+--------------------------------------+

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 
    MID('Drink your beer', 0) AS "0",
    MID('Drink your beer', 1) AS "1";

Result:

+-----------------+-----------------+
| 0               | 1               |
+-----------------+-----------------+
| Drink your beer | Drink your beer |
+-----------------+-----------------+

Here it is in default mode:

SET SQL_MODE=DEFAULT;
SELECT 
    MID('Drink your beer', 0) AS "0",
    MID('Drink your beer', 1) AS "1";

Result:

+------+-----------------+
| 0    | 1               |
+------+-----------------+
|      | Drink your beer |
+------+-----------------+

Null Arguments

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

SELECT 
    MID(null, 3, 3),
    MID('Beer', null, 3),
    MID('Beer', 3, null),
    MID(null, null, null);

Result:

+-----------------+----------------------+----------------------+-----------------------+
| MID(null, 3, 3) | MID('Beer', null, 3) | MID('Beer', 3, null) | MID(null, null, null) |
+-----------------+----------------------+----------------------+-----------------------+
| NULL            | NULL                 | NULL                 | NULL                  |
+-----------------+----------------------+----------------------+-----------------------+

Missing Arguments

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

SELECT MID();

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