How To do Case Sensitive String Match in SQL Server

Introduction

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 enters password as “admin” or “ADMIN” even though 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!