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?