Why does RIGHT(@foostr, 0) return NULL when @foostr is varchar(max)?
In SQL Server 2005
If I want to find the right-most one character of a varchar(max) variable, no problem:
declare @foostr varchar(max)
set @foostr = 'abcd'
select right (@foostr, 1)
----
d
If I want to find the right-most zero characters of a string literal, no problem:
select right ('abcd', 0)
------------------
It returns an empty string.
If I want to find the right-most zero characters of a varchar(10), no problem:declare @foostr varchar(10)
set @foostr = 'abcd'
select right (@foostr, 0)
----
It returns an empty string.
If I want to find the right-most zero characters of a varchar(max), well:decl开发者_如何学编程are @foostr varchar(max)
set @foostr = 'abcd'
select right (@foostr, 0)
----
NULL
It returns NULL. Why?
I think this is an undefined behavior, as described in the doc of RIGHT
,
integer_expression
Is a positive integer that specifies how many characters of character_expression will be returned. If integer_expression is negative, an error is returned.
It does not specify what will return if it is 0.
,1 not ,0 but returns '' for me anyway
declare @foostr varchar(max)
set @foostr = 'abcd'
select right (@foostr, 1)
why ,0 ???? what is the right most zero characters supposed to mean?
RIGHT ( character_expression , integer_expression ) integer_expression Is a positive integer that specifies how many characters of character_expression will be returned. If integer_expression is negative, an error is returned. If integer_expression is type bigint and contains a large value, character_expression must be of a large data type such as varchar(max).
精彩评论