substring, substr
Description
Extracts characters staring from the specified position and returns a substring of specified length.
Syntax
VARCHAR substr(VARCHAR str, pos[, len])
Parameters
str
: required, the string to extract characters. It must be a VARCHAR value.pos
: required, an integer that specifies the starting position. Note that the first character in a string is 1, not 0.- If
pos
is 0, an empty string is returned. pos
can be a negative integer. In this case, this function extracts characters starting from the end of the string. See Example 2.- If the position specified by
pos
exceeds the range of the string, an empty string is returned. See Example 3.
- If
len
: optional, a positive integer that specifies the number of characters to extract.- If
len
is specified, this function extractslen
characters starting from the position specified bypos
. - If
len
is not specified, this function extracts all the characters starting atpos
. See Example 1. - If
len
exceeds the actual length of the matching characters, all the matching characters are returned. See Example 4.
- If
Return value
Returns a value of the VARCHAR type.
Examples
-- Extract all characters starting from the first character "s".
MySQL > select substring("starrockscluster", 1);
+-------------------------------------+
| substring('starrockscluster', 1) |
+-------------------------------------+
| starrocks |
+-------------------------------------+
-- The position is negative and the counting is from the end of the string.
MySQL > select substring("starrocks", -5, 5);
+-------------------------------+
| substring('starrocks', -5, 5) |
+-------------------------------+
| rocks |
+-------------------------------+
-- The position exceeds the length of the string and an empty string is returned.
MySQL > select substring("apple", 8, 2);
+--------------------------------+
| substring('apple', 8, 2) |
+--------------------------------+
| |
+--------------------------------+
-- There are 5 matching characters. The length 9 exceeds the length of the matching characters and all the matching characters are returned.
MySQL > select substring("apple", 1, 9);
+--------------------------+
| substring('apple', 1, 9) |
+--------------------------+
| apple |
+--------------------------+
keyword
substring,string,sub