MariaDB SUBSTRING() Function

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

SUBSTR() is a synonym for SUBSTRING().

MariaDB SUBSTRING() Syntax

The MariaDB SUBSTRING() function haves 4 syntax forms:

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

Both forms of using the FROM keyword are standard SQL syntax.

The MID(str, pos, len) is equivalent to SUBSTRING(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 it is a positive number, the substring is extracted from the beginning of the string as the starting position determines the starting position to extract. If it is a negative number, the substring is extracted from the end of the string as the starting position determines the starting position for extraction.

len

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

If you don’t provide any 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

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

If pos is 0 or exceeds the length of the original string, the SUBSTRING() function will return an empty string ''. If in Oracle mode, 0 and 1 are the same for pos.

If pos is negative, the SUBSTRING() function counts forward from the end of the string.

If pos + len exceeds the length of the original string, the SUBSTRING() function extracts to the end of the original string.

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

MariaDB SUBSTRING() Examples

Basic usage

This statement shows the basic usage of the MariaDB SUBSTRING() function:

SELECT SUBSTRING('Hello World', 7);

Output:

+-----------------------------+
| SUBSTRING('Hello World', 7) |
+-----------------------------+
| World                       |
+-----------------------------+

The current statement shows different ways to achieve the same effect:

SELECT
  SUBSTRING('Hello World', 7),
  SUBSTRING('Hello World', 7, 5),
  SUBSTRING('Hello World' FROM 7),
  SUBSTRING('Hello World' FROM 7 FOR 5)\G

Output:

          SUBSTRING('Hello World', 7): World
       SUBSTRING('Hello World', 7, 5): World
      SUBSTRING('Hello World' FROM 7): World
SUBSTRING('Hello World' FROM 7 FOR 5): World

Negative position

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

SELECT SUBSTRING('Hello World', -5);

Output:

+------------------------------+
| SUBSTRING('Hello World', -5) |
+------------------------------+
| World                        |
+------------------------------+

Conclusion

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