SUBSTRING

Returns part of a character, binary, text, or image expression.

Syntax

SUBSTRING ( expression, start, length )

Arguments

expression

Is a character, binary, text, ntext, or image expression.

start

Is a integer or bigint expression that specifies where the returned characters start. (The numbering is 1 based, meaning that the first character in the expression is 1). If start is less than 1, the returned expression will begin at the first character that is specified in expression. In this case, the number of characters that are returned is the largest value of either the sum of start + length- 1 or 0. If start is greater than the number of characters in the value expression, a zero-length expression is returned.

length

Is a positive integer or bigint expression that specifies how many characters of the expression will be returned. If length is negative, an error is generated and the statement is terminated. If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned.

Return types

Returns character data if expression is one of the supported character data types. Returns binary data if expression is one of the supported binary data types. The returned string is the same type as the specified expression with the exceptions shown in the table.

Specified expression Return type
char/varchar/text varchar
nchar/nvarchar/ntext nvarchar
binary/varbinary/image varbinary

Examples

SELECT SUBSTRING(name, 3, 2)
  FROM sys.databases;

See Also