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
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/
Geert Schuring
October 24, 2008 at 10:19 am
yea I read the post over there too, its interesting but, I ‘think’ Sql Server doesn’t have such an option.
imak47
October 30, 2008 at 8:57 am