The SQL Procedure

SUBSTRING function

Returns a part of a character expression.

SUBSTRING (sql-expression FROM start <FOR length>)

sql-expression must be a character string and is described in sql-expression.
start is a number (not a variable or column name) that specifies the position, counting from the left end of the character string, at which to begin extracting the substring.
length is a number (not a variable or column name) that specifies the length of the substring that is to be extracted.

The SUBSTRING function operates on character strings. SUBSTRING returns a specified part of the input character string, beginning at the position that is specified by start. If length is omitted, then the SUBSTRING function returns all characters from start to the end of the input character string. The values of start and length must be numbers (not variables) and can be positive, negative, or zero.

If start is greater than the length of the input character string, then the SUBSTRING function returns a zero-length string.

If start is less than 1, then the SUBSTRING function begins extraction at the beginning of the input character string.

If length is specified, then the sum of start and length cannot be less than start or an error is returned. If the sum of start and length is greater than the length of the input character string, then the SUBSTRING function returns all characters from start to the end of the input character string. If the sum of start and length is less than 1, then the SUBSTRING function returns a zero-length string.

Note: The SUBSTRING function is provided for compatibility with the ANSI SQL standard. You can also use the SAS function SUBSTR. [cautionend]

Top of Page