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#
##