How to do case sensitive comparision in SQL server

Case sensitive string comparison can be done using collation in WHERE statement or by specifying collation for column.

See below example which uses COLLATE keyword while comparing two string values in WHERE clause. This example uses SQL_Latin1_General_CP1_CS_AS. If your data is not in English, then choose appropriate collation.

CREATE TABLE testUserTable (Username VARCHAR(30), pwd VARCHAR(30))

INSERT INTO testUserTable(Username, pwd)

      VALUES('User 1', 'PassWord1'), ('User 2', 'PassWord2')

DECLARE @username VARCHAR(30)= 'User 1'

      ,@pwd VARCHAR(30)= 'password1'

--Query 1: This query will return result

SELECT *

FROM testUserTable

WHERE Username = @username

      AND pwd = @pwd

-- Query 2: This query will not return result

SELECT *

FROM testUserTable

WHERE Username = @username

      AND pwd = @pwd COLLATE SQL_Latin1_General_CP1_CS_AS

DROP TABLE testUserTable

First SELECT query will return result even if password is supplied in lower case. Second query will not return any record, which is actually what we wanted in this particular case.

Here in our example we have specified _CS_ (case sensitive) in our collation selection. This behaviour can be reverted to previous by setting it to _CI_ (case insensitive). This will give use the same result as we get in Query 1 because default collation setting in _CI_.

See below CREATE statement which uses collation while creating table.

CREATE TABLE testUserTable (Username VARCHAR(30), pwd VARCHAR(30) COLLATE SQL_Latin1_General_CP1_CS_AS)

In this case we don’t have to specify collation in where clause. Both SELECT queries will give the same result.