Skip to main content
Version: 2.5

substring, substr

Description​

Extracts characters from a string and returns a substring.

If len is not specified, this function extracts characters from the position specified by pos. If len is specified, this function extracts len characters from the position specified by pos.

pos can be a negative integer. In this case, this function extracts characters starting from the end of the string.

Syntax​

VARCHAR substr(VARCHAR str, pos[, len])

Parameter values​

  • str: the string to extract characters, required. It must be a VARCHAR value.
  • pos: the start position, required. The first position in the string is 1.
  • length: the number of characters to extract, optional. It must be a positive integer.

Return value​

Returns a value of the VARCHAR type.

If the number of characters (len) to return exceeds the actual length of the matching characters, all the matching characters are returned.

If the position specified by pos exceeds the range of the string, an empty string is returned.

Examples​

MySQL > select substring("starrockscluster", 1, 9);
+-------------------------------------+
| substring('starrockscluster', 1, 9) |
+-------------------------------------+
| starrocks                           |
+-------------------------------------+

MySQL > select substring("starrocks", -5, 5);
+-------------------------------+
| substring('starrocks', -5, 5) |
+-------------------------------+
| rocks                         |
+-------------------------------+

MySQL > select substring("apple", 1, 9);
+--------------------------+
| substring('apple', 1, 9) |
+--------------------------+
| apple                    |
+--------------------------+

keyword​

substring,string,sub