MariaDB MID() Function

In MariaDB, MID() is a built-in string function that extracts a substring of a specified length from a string starting from a specified position.

MariaDB MID() Syntax

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

MID(str, pos[, len])

MID(str, pos, len) is equivalent to SUBSTRING(str, pos, len) and SUBSTR(str, pos, len).

Parameters

str

Required. The original string from which to extract the substring.

pos

Required. The location to start extraction from. It can be positive or negative. If positive, substrings are extracted starting from the beginning of the string. If negative, a substring is extracted from the end of the string.

len

Optional. The length (number of characters) of the substring. If not specified, extracts to the end of the original string.

If you use wrong parameters, MariaDB will report an error: 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.

Return value

MariaDB MID() function returns a substring extracted from the original string.

If pos is 0 or exceeds the length of the original string, the MID() function will return an empty string ''.

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

MariaDB MID() Examples

Extract substring

The following statement shows how to use the MariaDB MID() function to extract a substring from the third character to the end of 'Hello':

SELECT MID('Hello', 3);

Output:

+-----------------+
| MID('Hello', 3) |
+-----------------+
| llo             |
+-----------------+

Extract a substring of a specified length

The following statement shows how to use MariaDB MID() function to extract a substring of a specified length from a specified position:

SELECT MID('Hello', 1, 2);

Output:

+--------------------+
| MID('Hello', 1, 2) |
+--------------------+
| He                 |
+--------------------+

In this example, MID('Hello', 1, 2) extracts a substring of length 2 starting from the first character , so it returns He.

Other examples

This example shows various uses of the MariaDB MID() function:

SELECT
    MID('Hello', 1, 2),
    MID('Hello', 2, 2),
    MID('Hello', 1),
    MID('Hello', 1, 8),
    MID('Hello', -1, 2),
    MID('Hello', 8, 2),
    MID('Hello', 0, 2),
    MID('Hello', 0),
    MID(NULL, 1, 1)\G

Output:

*************************** 1\. row ***************************
 MID('Hello', 1, 2): He
 MID('Hello', 2, 2): el
    MID('Hello', 1): Hello
 MID('Hello', 1, 8): Hello
MID('Hello', -1, 2): o
 MID('Hello', 8, 2):
 MID('Hello', 0, 2):
    MID('Hello', 0):
    MID(NULL, 1, 1): NULL

Conclusion

The MariaDB MID() function extracts a substring of a specified length from a specified position of a string and returns it.