12/05/2014 by Nitesh

How To do Case Sensitive String Match in SQL Server

Friends,

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!

#SQL#SQL Server