SUBSTRING
Syntax
SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)
Description
The forms without a len
argument return a substring from string str
starting at position pos
.
The forms with a len
argument return a substring len
characters long from string str
, starting at position pos
.
The forms that use FROM
are standard SQL syntax.
It is also possible to use a negative value for pos
. In this case, the beginning of the substring is pos
characters from the end of the string, rather than the beginning. A negative value may be used for pos
in any of the forms of this function.
For all forms of SUBSTRING()
, the position of the first character in the
string from which the substring is to be extracted is reckoned as 1.
If any argument is NULL
, returns NULL
.
From MariaDB 10.3.3, when sql_mode='oracle', for Oracle-compatibility, position zero is treated as position one.
Examples
SELECT SUBSTRING('Knowledgebase',5); +------------------------------+ | SUBSTRING('Knowledgebase',5) | +------------------------------+ | ledgebase | +------------------------------+ SELECT SUBSTRING('MariaDB' FROM 6); +-----------------------------+ | SUBSTRING('MariaDB' FROM 6) | +-----------------------------+ | DB | +-----------------------------+ SELECT SUBSTRING('Knowledgebase',3,7); +--------------------------------+ | SUBSTRING('Knowledgebase',3,7) | +--------------------------------+ | owledge | +--------------------------------+ SELECT SUBSTRING('Knowledgebase', -4); +--------------------------------+ | SUBSTRING('Knowledgebase', -4) | +--------------------------------+ | base | +--------------------------------+ SELECT SUBSTRING('Knowledgebase', -8, 4); +-----------------------------------+ | SUBSTRING('Knowledgebase', -8, 4) | +-----------------------------------+ | edge | +-----------------------------------+ SELECT SUBSTRING('Knowledgebase' FROM -8 FOR 4); +------------------------------------------+ | SUBSTRING('Knowledgebase' FROM -8 FOR 4) | +------------------------------------------+ | edge | +------------------------------------------+
SHOW VARIABLES LIKE 'sql_mode%'; +---------------+-------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------------------------------------------------------+ | sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+-------------------------------------------------------------------------------------------+ select substr('abc',0,3); +-------------------+ | substr('abc',0,3) | +-------------------+ | | +-------------------+ SET sql_mode='oracle'; Query OK, 0 rows affected (0.000 sec) select substr('abc',0,3); +-------------------+ | substr('abc',0,3) | +-------------------+ | abc | +-------------------+