SUBSTRING (TSQL Function)

Returns a portion of a string.

Syntax

SUBSTRING ( string_expression , start , length )

Arguments

string_expression
is a string expression of type varchar.
start
specifies the start position (1-based) of the substring to return. It is of type int.
length
is the length of the substring to return. It is of type int.

Return Types

Returns varchar.

The collation of the returned string is the same as string_expression.

Remarks

If start <= 0, the first character of the returned string will be the first character of string_expression.
In this case, the length of the returned string will be start + length- 1, or 0 if the latter is negative.

If start is longer than string_expression, an empty string is returned.

Examples

PRINT '#' + SUBSTRING('abcdef',  1, 30) + '#';
PRINT '#' + SUBSTRING('abcdef',  2, 30) + '#';
PRINT '#' + SUBSTRING('abcdef',  5, 30) + '#';
PRINT '#' + SUBSTRING('abcdef',  6, 30) + '#';
PRINT '#' + SUBSTRING('abcdef',  7, 30) + '#'; -- returned substring is an empty string

The result is:

#abcdef#
#bcdef#
#ef#
#f#
##

Examples with start <= 0

PRINT '#' + SUBSTRING('abcdef',  1, 3) + '#';
PRINT '#' + SUBSTRING('abcdef',  0, 3) + '#';
PRINT '#' + SUBSTRING('abcdef', -1, 3) + '#';
PRINT '#' + SUBSTRING('abcdef', -2, 3) + '#';

The result is:

#abc#
#ab#
#a#
##