Right function doesn't handle ending whitespaces?

Have problem with the RIGHT function.

SELECT LEN(RIGHT('ABCD ',5))

Above return 4, but I expects 5. Is this a bug? The compability level i 90

Thanks,

John

The len function automatically trim the spaces on the tail end of the string.|||

The result from this one is "ABCD " with a withspace after D.

SELECT '"' + RIGHT('ABCD ',5) + '"'

How can LEN do so? It isn't a true value|||

Ok in SQL Server all the following values are equal..(while compare)

'ABCD'

'ABCD '

'ABCD '

'ABCD '

'ABCD '

Bcs SQL Server never consider the white space (any number of) at the end of the string. (while compare)

So if all these strings are equal then the length of the each string also equal ie, 4.

Got it?

|||

It depends what kind of operator or function you are using.

select 1

where'ABCD'like'ABCD '

select datalength('ABCD ')

go

AMB

|||Yep, use datalength() if you are interested in counting trailing blanks as part of the length.|||

Mani,

That's not complete accurate. SQL Server knows the values are NOT equal. The LEN() is designed to ignore the trailing spaces.

For example, using the values you have above:

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
MyValue varchar(20)
)

INSERT INTO @.MyTable VALUES ( 'ABCD' )
INSERT INTO @.MyTable VALUES ( 'ABCD ' )
INSERT INTO @.MyTable VALUES ( 'ABCD ' )
INSERT INTO @.MyTable VALUES ( 'ABCD ' )
INSERT INTO @.MyTable VALUES ( 'ABCD ' )

SELECT SpaceNotTrimmed = ( MyValue + '<--' )
FROM @.MyTable

SpaceNotTrimmed
--
ABCD<--
ABCD <--
ABCD <--
ABCD <--
ABCD <--

As you can see from the example, the trailing spaces are definitely not ignored -they are there.

However the LEN() function does 'ignore' the trailing spaces, if you need to know the character count for a varchar field, you need to use the DATALENGTH() function.

This is one of those 'ANSI' related pecularities...

|||Thanks for all help!sql