Why select ... where 'Anna ' = 'Anna' returns TRUE?

[Robert Zientara]

Sorry for asking stupid questions...

I can't remember which settings in MS SQL define the behaviour of this comparison:

select * from table where 'Anna ' = 'Anna'

to be TRUE. Both strings are different because the first contains trailing blanks.

How to change it to return FALSE what is my expected value?

[Simon Hayes]

ae***@op.pl (Robert Zientara) wrote in message news:<de**************************@posting.google. com>...

Sorry for asking stupid questions...

I can't remember which settings in MS SQL define the behaviour of this comparison:

select * from table where 'Anna ' = 'Anna'

to be TRUE. Both strings are different because the first contains trailing blanks.

How to change it to return FALSE what is my expected value?

You may be thinking of SET ANSI_PADDING, but that applies to how
strings are stored in tables, not how they are compared. SQL Server
ignores trailing blanks for non-Unicode data:

http://support.microsoft.com/default...;en-us;Q316626

It would be useful to know the data types of the columns/variables
you're working with, but one possible query to get only exact matches
(on the basis of the length of the string) might be this:

select *
from table
where column = 'Anna' and
datalength(column) = datalength('Anna')

Simon

[mountain man]

"Robert Zientara" <ae***@op.pl> wrote in message
news:de**************************@posting.google.c om...

Sorry for asking stupid questions...

I can't remember which settings in MS SQL define the behaviour of this comparison: select * from table where 'Anna ' = 'Anna'
to be TRUE. Both strings are different because the first contains trailing blanks. How to change it to return FALSE what is my expected value?

Check .... SET ANSI_PADDING

Also RTRIM

Pete Brown
Falls Creek
Oz