SUBSTR and SUBSTRING
Given a string, the SUBSTR and SUBSTRING functions return the substring starting from the start position, and extending up to the length specified by the substring length.
Syntax
SUBSTR (string, start_position, length_of_substring)
SUBSTRING (string, start_position, length_of_substring)
Remarks
• Start_position and length_of_substring must be positive integers.
• The original string is assumed to start at position one (1).
• The resulting substring is any sequence of characters in the original string, including an empty string.
• If the original string is an empty string, the resulting substring is also an empty string.
• If any of the input arguments is NULL, the output is also NULL.
The following table lists the input types that you can use in SUBSTRING, and their corresponding output types.
Data Type of
string | Data Type of start_position | Data Type of length_of_substring | Data Type of Output |
CHAR | TINYINT | Same as start_position. | Same as string argument. |
LONGVARCHAR | INTEGER |
STRING | BIGINT |
VARCHAR | SMALLINT |
NULL | BIGINT | Same as start_position. | NULL |
INTEGER |
NULL |
SMALLINT |
TINYINT |
CHAR | NULL | TINYINT | NULL |
LONGVARCHAR | INTEGER |
STRING | BIGINT |
VARCHAR | SMALLINT |
CHAR | TINYINT | NULL | NULL |
LONGVARCHAR | INTEGER |
STRING | BIGINT |
VARCHAR | SMALLINT |
Example
SELECT SUBSTRING (customers.PhoneNumber, 1, 5) AreaCode