开发者

Why does RIGHT(@foostr, 0) return NULL when @foostr is varchar(max)?

开发者 https://www.devze.com 2023-01-03 05:53 出处:网络
In SQL Server 2005 If I want to find the right-most one character of a varchar(max) variable, no problem:

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).

0

精彩评论

暂无评论...
验证码 换一张
取 消