Case-Sensitive Comparisons

[Sunite]

Case-Sensitive Comparisons



Q
How can I program a case-sensitive comparison of a user-typed password on a case-insensitive SQL Server 7.0 instance?



A

If you upgrade your system to SQL Server 2000, you can specify data collation down to the column level. (The SQL Server 2000 Books Online glossary defines collation as "a set of rules that determines how data is compared, ordered, and presented. Character data is sorted using collation information, including locale, sort order, and case-sensitivity.")

However, until you upgrade to SQL Server 2000, you can use the following technique. Assume that the value of the password stored in your table is BamBi2000 (notice that the "B"s are uppercase, whereas all other letters are lowercase):

DECLARE @user_password varchar(12)

IF CAST (@user_password AS varbinary(12)) =
CAST ('BamBi2000' AS varbinary(12))
PRINT 'Password match'
ELSE
PRINT 'Password mismatch'