SQL - Why is parameter trimmed

Question

I have a table (t) with a column (c) defined as varchar 20, containing the values 'a', 'b' and 'c'

If I execute the following sql, a value is returned:

select * from table where c = 'a    '

Why?

I assume it's casting/trimming the parameter in some way, but why?

How would I really search for 'a '

Answer 1

To compare it to a varchar column, your string literal must also be a varchar type. Varchar does trim extra ending white space. It varies, hence VARchar. The plain the char type does not trim the whitespace, and will actually append extra spaces on the end of a literal if need be. You can check for this (and fix your search with the LEN() function:

 declare @a varchar(20) = 'a     '
 select * from t where c = @a and len(@a) = Len(c)

Answer 2

Trailing space is specified to be handled in a special way in the ANSI SQL Standard:

http://support.microsoft.com/default.aspx/kb/316626

http://support.microsoft.com/kb/154886/EN-US/

This behavior is effectively the same for both char and varchar.