Skip to content

substr

Returns the portion of the string value from <base_expr>, starting from the character specified by <start_expr>, with optionally limited length.

substr( <base_expr>, <start_expr> [ , <length_expr> ] )

An expression that evaluates to a string value.

An expression that evaluates to an integer. It specifies the offset from which the substring starts. The offset is measured in the number of UTF-8 characters.

The start position is 0-based.

An expression that evaluates to an integer. It specifies the number of UTF-8 characters to return.

Specify a length that is greater than or equal to zero. If the length is a negative number, the function throws an error.

The data type of the returned value is string. If any of the inputs are NULL, NULL is returned.

The following example uses the substr function to return the portion of the string that starts at the ninth character and limits the length of the returned value to three characters:

SELECT substr('testing 1 2 3', 8, 3);
+-------------------------------+
| substr('testing 1 2 3', 8, 3) |
+-------------------------------+
| 1 2 |
+-------------------------------+

The following example shows the substrings returned for the same <base_expr> when different values are specified for <start_expr> and <length_expr>:

VALUES
('mystring', -1, 3),
('mystring', -3, 3),
('mystring', -3, 7),
('mystring', -5, 3),
('mystring', -7, 3),
('mystring', 0, 3),
('mystring', 0, 7),
('mystring', 1, 3),
('mystring', 1, 7),
('mystring', 3, 3),
('mystring', 3, 7),
('mystring', 5, 3),
('mystring', 5, 7),
('mystring', 7, 3),
('mystring', NULL, 3),
('mystring', 3, NULL)
SELECT
$0 as base_value,
$1 as start_value,
$2 as length_value,
substr($0, $1, $2) as substring;
+------------+-------------+--------------+-----------+
| base_value | start_value | length_value | substring |
+------------+-------------+--------------+-----------+
| mystring | -1 | 3 | g |
| mystring | -3 | 3 | ing |
| mystring | -3 | 7 | ing |
| mystring | -5 | 3 | tri |
| mystring | -7 | 3 | yst |
| mystring | 0 | 3 | mys |
| mystring | 0 | 7 | mystrin |
| mystring | 1 | 3 | yst |
| mystring | 1 | 7 | ystring |
| mystring | 3 | 3 | tri |
| mystring | 3 | 7 | tring |
| mystring | 5 | 3 | ing |
| mystring | 5 | 7 | ing |
| mystring | 7 | 3 | g |
| mystring | NULL | 3 | NULL |
| mystring | 3 | NULL | NULL |
+------------+-------------+--------------+-----------+
ScopeDB Logo ScopeDB
© 2025 ScopeDB. All rights reserved.