tags:

views:

105

answers:

2

I want to do a case sensitive search in my SQL query. But by default, MS SQL server does not consider the case of the strings.

Any idea on how to do a case sensitive search in SQL query?

+5  A: 

Can be done via changing the Collation, by default it is case insensitive:

http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm

Beginner
thanks. it worked.
Veera
+5  A: 

By using collation or casting to binary, like this:

SELECT *
FROM Users
WHERE  
 Username = @Username COLLATE SQL_Latin1_General_CP1_CS_AS
 AND Password = @Password COLLATE SQL_Latin1_General_CP1_CS_AS
 AND Username = @Username 
 AND Password = @Password

The duplication of username/password exists to give the engine the possibility of using indexes. The collation above is a Case Sensitive collation, change to the one you need if necessary.

The second, casting to binary, could be done like this:

SELECT *
FROM Users
WHERE  
 CAST(Username as varbinary(100)) = CAST(@Username as varbinary))
 AND CAST(Password as varbinary(100)) = CAST(@Password as varbinary(100))
 AND Username = @Username 
 AND Password = @Password
Jonas Lincoln