Returns a substring that starts from start_position in a string specified by str and has a length specified by length.
Syntax
string substring(string|binary <str>, int <start_position>[, int <length>])Parameters
str: required. A value of the STRING or BINARY type.
start_position: required. A value of the INT type. The start position starts from 1. If start_position is set to 0, an empty string is returned. If start_position is set to a negative value, the search starts from the end to the start of the string and the last character is -1.
length: optional. A value of the BIGINT type. This parameter specifies the length of a substring. The value of this parameter must be greater than 0.
Return value
A value of the STRING type is returned. The return value varies based on the following rules:
If the value of str is not of the STRING or BINARY type or cannot be implicitly converted into a value of the STRING or BINARY type, an error is returned.
If the value of length is not of the BIGINT type or is less than or equal to 0, an error is returned.
If length is not specified, the substring from the start position to the end of the string specified by str is returned.
If the value of str, start_position, or length is null, null is returned.
Examples
Example 1: Return a substring that starts from the specified position in the string
abcand has the specified length. Sample statement:-- The return value is bc. select substring('abc', 2); -- The return value is b. select substring('abc', 2, 1); -- The return value is bc. select substring('abc',-2,2); -- The return value is ab. select substring('abc',-3,2); -- The return value is 001. select substring(bin(2345), 2, 3);Example 2: An input parameter is set to null. Sample statement:
-- The return value is null. select substring('abc', null, null);
Related functions
SUBSTRING is a string function. For more information about functions related to string searches and conversion, see String functions.