views:

41

answers:

2

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:

declare @foostr varchar(max)
set @foostr = 'abcd'
select right (@foostr, 0)

----    
NULL

It returns NULL. Why?

A: 

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

SQLMenace
+4  A: 

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.

KennyTM
the doc of RIGHT !
Andomar
Still, it's pretty odd that NULL is returned. Asking for 0 characters ought to give you a 0-length string.
I. J. Kennedy