There are lot many options to achieve case sensitive search in sql. Most of today’s password are now case sensitive but sql directly doesn’t support case sensitive search at all. Here in this post i will explain best options to achieve case sensitive search. We will go through binary conversation and SQl Collation turn by turn.

1. Using binary type conversion while comparison

Each character in sql have a different ASCII value. So for sql, ASCII value for “E” is different then “e”. We can use this ASCII conversion to compare our case sensitive values. We will convert both of our comparing values to ASCII while comparison.

So lets first check what we are exactly trying to do. Have a look at the below simple sql script. Both of the values are not equal if we will see it in terms of case sensitivity. But if your try to run below script in sql server it will print “Matched”. So sql will treat both of them as a equal and ignore the case sensitive characters.

DECLARE @VALUE VARCHAR(20)
DECLARE @COMPARE_VALUE VARCHAR(20)

SET @VALUE = 'aBcDEf'
SET @COMPARE_VALUE = 'abcdef'

IF @VALUE = @COMPARE_VALUE
	BEGIN
		PRINT 'Matched'
	END
ELSE
	BEGIN
		PRINT 'Not Matched'
	END

Output : Matched

Now to compare both of the values with case sensitivity lets rewrite above script to convert both of the values in ASCII while comparison.

DECLARE @VALUE VARCHAR(20)
DECLARE @COMPARE_VALUE VARCHAR(20)

SET @VALUE = 'aBcDEf'
SET @COMPARE_VALUE = 'abcdef'

IF CAST(@VALUE AS VARBINARY(20)) = CAST(@COMPARE_VALUE AS VARBINARY(20))
	BEGIN
		PRINT 'Matched'
	END
ELSE
	BEGIN
		PRINT 'Not Matched'
	END

Output : Not Matched

So in our where condition we can convert both of our values to VARBINARY while comparison something like below.

SELECT UserId,UserName,Email,Address
FROM UserMaster
WHERE CAST(UserName AS VARBINARY(20)) = CAST(@UserName AS VARBINARY(20))
AND CAST(Password AS VARBINARY(20)) = CAST(@Password AS VARBINARY(20))

2. Using SQL Collation

SQL collation is a way to define the use of characters for languages or to a string to apply collation cast.  In simple word its just the matter of ASCII ordering.

So lets go through the example to check how can we use SQL Collation for case sensitive search.

DECLARE @VALUE VARCHAR(20)
DECLARE @COMPARE_VALUE VARCHAR(20)

SET @VALUE = 'aBcDEf'
SET @COMPARE_VALUE = 'abcdef'

-------------------Simple comparison------------------------
IF @VALUE = @COMPARE_VALUE
	BEGIN
		PRINT 'Result 1 - Matched'
	END
ELSE
	BEGIN
		PRINT 'Result 1 - Not Matched'
	END
--------------------SQL Collation---------------------------
IF @VALUE = @COMPARE_VALUE COLLATE SQL_Latin1_General_CP1_CS_AS
	BEGIN
		PRINT 'Result 2 - Matched'
	END
ELSE
	BEGIN
		PRINT 'Result 2 - Not Matched'
	END

Output :

Result 1 – Matched
Result 2 – Not Matched

So in the above example the first comparison is simple sql comparison where we are just  comparing both values without using any kind of collation. The result will show both of the values are same and completely ignoring the case sensitive characters. Now the second result where we have used COLLATE SQL_Latin1_General_CP1_CS_AS to compare both values does not match the values and completely handling case sensitivity.