Friday, January 23, 2009

How to perform case sensitive searches in SQL Server?

A default SQL Server installation is case insensitive, which means that SQL Server will not differentiate between upper and lower case characters/letters. That is, "Magic" is the same as "MAGIC" or "magic".
Let’s see why one would want to perform case sensitive searches. One classic example is password comparisons. People use a combination of upper and lower case (mixed case) characters in their passwords, just to make the passwords difficult to guess. But there is no point in doing that, if the database disregards the case.
To keep the users happy, and their passwords secure, programmers prefer case sensitive comparisons in this case.
So we can achieve the goal by Converting data to binary type before comparison:When you convert a character to binary or varbinary datatype, that character's ASCII value gets represented in binary. Since, 'A' and 'a' have different ASCII values, when you convert them to binary, the binary representations of these values don't match, and hence the case sensitive behavior.
Example :



IF EXISTS
(SELECT 1 FROM AuthenticCustomers
WHERE CAST(UserID AS varbinary(20)) = CAST( AS varbinary(20))
AND CAST(User_Password AS varbinary(25)) = CAST( AS varbinary(25)))

BEGIN
PRINT 'Authentic User'
END
ELSE
BEGIN
PRINT 'Invalid User or Password'
END