Home > SQL Server 2005 > How to make a case sensitive comparision of strings in SQL Server

How to make a case sensitive comparision of strings in SQL Server

String in SQL Server are by default case insensitive. That means that “SQL” is equivalent to “sql” and it causes problems when you’re making a password authentication mechanism which is supposed to be case senstive. Here’s an example stored procedure in which I’m making a case sensitive string comparison for the password field using Collations.

CREATE PROCEDURE [dbo].[proc_AuthenticateUser]
(
@UserName varchar(50),
@Password varchar(50)
)
AS
BEGIN

SET NOCOUNT ON
DECLARE @Err int

SELECT     dbo.[User].Id
FROM         dbo.[User]
WHERE     (dbo.[User].Password = @Password  COLLATE SQL_Latin1_General_CP1_CS_AS) AND (dbo.[User].Username = @UserName)
SET @Err = @@Error

RETURN @Err
END

  1. October 24, 2008 at 10:19 am | #1

    You could ofcourse just set the server default collation method to a binary method.

    See: http://geertschuring.wordpress.com/2008/10/14/why-mysql-gave-case-insensitive-string-results/

  2. imak47
    October 30, 2008 at 8:57 am | #2

    yea I read the post over there too, its interesting but, I ‘think’ Sql Server doesn’t have such an option.

  1. No trackbacks yet.