SUBSTRING
On this page
Extracts a range of characters from a string.
Syntax
SUBSTRING(str, start, len)SUBSTR(str, start, len)SUBSTRING(str, start)SUBSTR(str, start)SUBSTRING(str FROM start FOR len)SUBSTR(str FROM start FOR len)SUBSTRING(str FROM start)SUBSTR(str FROM start)
Arguments
-
str: any string or binary object
-
start: the one-indexed position to start at.
If negative, counts from the end. -
len: length is the number of characters to extract.
Forms without len, extract characters until the end of the string.
Return Type
String
Remarks
-
In
SUBSTRorSUBSTRING, the starting index point of a string is1(0is an alias for1).
Examples
In the following example, the starting index 3 represents the third character in the string, because the index starts from 1.
SELECT SUBSTRING('1234567890', 3, 3);
+-------------------------------+
| SUBSTRING('1234567890', 3, 3) |
+-------------------------------+
| 345 |
+-------------------------------+In the following example, the substring is returned from the end, because the starting index is a negative number.
SELECT SUBSTRING('1234567890', -3, 3);
+--------------------------------+
| SUBSTRING('1234567890', -3, 3) |
+--------------------------------+
| 890 |
+--------------------------------+The following examples demonstrate how substring works using different lengths and starting positions.
CREATE TABLE sub_tab(a INT, b INT, c INT, d INT);INSERT INTO sub_tab VALUES(1234,1234,1234,1234),(1,2,4, NULL);
SELECT substr(a,0,1) FROM sub_tab;
+---------------+
| substr(a,0,1) |
+---------------+
| 1 |
| 1 |
+---------------+SELECT substr(a,1,1) FROM sub_tab;
+---------------+
| substr(a,1,1) |
+---------------+
| 1 |
| 1 |
+---------------+SELECT substr(a,1,2) FROM sub_tab;
+---------------+
| substr(a,1,2) |
+---------------+
| 1 |
| 12 |
+---------------+SELECT substr(a,0,2) FROM sub_tab;
+---------------+
| substr(a,0,2) |
+---------------+
| 12 |
| 1 |
+---------------+Caution
Implicit Collation
When character_ is set to utf8, string literals with characters using 4-byte encoding are implicitly assigned binary collation and processed as a sequence of bytes rather than characters.utf8mb4 character set.
For more information, refer to Implicit Collation in Special Cases.
Related Topics
Last modified: June 12, 2023