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

Revoking and Granting permissions.. SQL Security Bug\Issue ?

I am getting a very strange issue occurring when granting
and revoking permissions on Roles in my database.
As part of the testing that I'm doing in a Role I revoked
the EXEC permission on a stored procedure and when
attempting to run the function in my VB app which
referenced this procedure I got the error
message 'EXECUTE permission denied on stored
procedure...' which makes perfect sense. But when going
back and granting the permission back to the role when I
run the same function in my VB app I get a number
of 'SELECT permission denied on object.' errors.
And these SELECT statements are in reference to what the
stored procedure is running against ? Is this an issue
with SQL security ?
Can anybody provide any insight ?Hi,
I feel that some body have denied select permission on your user. This will
over rule the grant access given to the role assigned to your user. This can
be overcomed by granding select permission to your user, so as the deny will
be removed.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:170c01c426ae$f400e1c0$a001280a@.phx.gbl...
> I am getting a very strange issue occurring when granting
> and revoking permissions on Roles in my database.
> As part of the testing that I'm doing in a Role I revoked
> the EXEC permission on a stored procedure and when
> attempting to run the function in my VB app which
> referenced this procedure I got the error
> message 'EXECUTE permission denied on stored
> procedure...' which makes perfect sense. But when going
> back and granting the permission back to the role when I
> run the same function in my VB app I get a number
> of 'SELECT permission denied on object.' errors.
> And these SELECT statements are in reference to what the
> stored procedure is running against ? Is this an issue
> with SQL security ?
> Can anybody provide any insight ?|||Permissions on objects referenced by your stored procedure are not checked
as long as the following are true:
1) the objects have the same owner (unbroken ownership chain)
2) you are not referencing objects using dynamic SQL in your proc.
3) if the objects are in different databases and you are running SQL 2000
SP3, cross-database chaining needs to be enabled in the databases involved
and the object owners need to map to the same login (per #1 above)
You can read more about ownership chains in the SQL 2000 Books Online
<adminsql.chm::/ad_security_4iyb.htm>.
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:170c01c426ae$f400e1c0$a001280a@.phx.gbl...
> I am getting a very strange issue occurring when granting
> and revoking permissions on Roles in my database.
> As part of the testing that I'm doing in a Role I revoked
> the EXEC permission on a stored procedure and when
> attempting to run the function in my VB app which
> referenced this procedure I got the error
> message 'EXECUTE permission denied on stored
> procedure...' which makes perfect sense. But when going
> back and granting the permission back to the role when I
> run the same function in my VB app I get a number
> of 'SELECT permission denied on object.' errors.
> And these SELECT statements are in reference to what the
> stored procedure is running against ? Is this an issue
> with SQL security ?
> Can anybody provide any insight ?