Microsoft's Strategic Changes in Technology and Business Practices to Expand Interoperability

On February 21, 2008 Microsoft announced a series of steps towards embracing a more open company.

You can read the whole announcement here: http://www.microsoft.com/presspass/press/2008/feb08/02-21ConCallTranscript.mspx

If they really do what they are saying then this will be the biggest game changer for the software industry since the launch of .Net platform by Microsoft.

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)

MS SQL Server VarChar comparison strips off trailing spaces

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