MariaDB SUBSTR() Function

In MariaDB, SUBSTR() 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 SUBSTR() Syntax

The MariaDB SUBSTR() function haves 4 syntax forms:

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

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

MID(str, pos, len) is equivalent to 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 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 SUBSTR() 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 SUBSTR() function will return an empty string ''. If in Oracle mode, 0 and 1 are the same for pos.

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

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

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

MariaDB SUBSTR() Examples

Basic usage

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

SELECT SUBSTR('Hello World', 7);

Output:

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

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

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

Output:

          SUBSTR('Hello World', 7): World
       SUBSTR('Hello World', 7, 5): World
      SUBSTR('Hello World' FROM 7): World
SUBSTR('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 SUBSTR('Hello World', -5);

Output:

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

Conclusion

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