How to Do Case Sensitive String Match in SQL server

While working on a SP today, I noticed that SQL Server does a case-insensitive string match in a query.

While working on a SP today, I noticed that SQL Server does a case-insensitive string match in a query. Hence in scenarios where passwords are to be validated, using a query as "WHERE Password =@Password" will give valid results if the user enter password as “admin” or

"ADMIN" eventhough when the password is set as "aDmiN". In this post, we will resolve this issue using a very simple method.

Let us consider below is our normal SQL procedure that validates a user from the tblUser table.

CREATE PROCEDURE [dbo].[tblUserSelect_Authenticate]
@Username nvarchar(50),
@Password nvarchar(50)
AS
BEGIN
SELECT * FROM tblUser WHERE Username = @Username AND Password = @Password
END

To tell SQL do a case-sensitive search, we will modify the above procedure as below. Please note we added a “COLLATE SQL_Latin1_General_CP1_CS_AS” to the field we want an exact match for.

CREATE PROCEDURE [dbo].[tblUserSelect_Authenticate]
@Username nvarchar(50),
@Password nvarchar(50)
AS
BEGIN
SELECT * FROM tblUser WHERE Username = @Username AND Password = @Password COLLATE SQL_Latin1_General_CP1_CS_AS
END

Keep learning and sharing! Cheers!