While comparing two varchars MSSQL Server strips off trailing spaces.

Why is that ? Surely 'Hello' <> 'Hello '. Other whitespace characters, like TAB, don't seem to do this.

One option I found was to do a
SET ANSI_PADDING ON

But this does not seem to work for comparisons :-(.

Fianlly got the comparison to work by using the following line:

WHERE CONVERT(varbinary,field) = CONVERT(varbinary,@compare_value)

With grateful thanks to Joel On Software. Refer link : http://discuss.joelonsoftware.com/default.asp?joel.3.66884.22


Above solution also has a flaw. It compares only first 'n' characters, I have not been able to figure out what the 'n' is (My guess is 8).
To compare the whole strings, we need to specify the length of the varbinary to convert to.
For example, in following line I am using SQL server MAX value which should work for all strings:
WHERE CONVERT(varbinary(MAX), field) = CONVERT(varbinary(MAX), @compare_value)