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?
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?
Can be done via changing the Collation, by default it is case insensitive:
http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm
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