How to do a case sensitive search in WHERE clause (I'm using SQL Server)?

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

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

11 Answers

Can be done via changing the Collation. By default it is case insensitive.

Excerpt from the link:

SELECT 1 FROM dbo.Customers WHERE CustID = @CustID COLLATE SQL_Latin1_General_CP1_CS_AS AND CustPassword = @CustPassword COLLATE SQL_Latin1_General_CP1_CS_AS 

Or, change the columns to be case sensitive.

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 

You can make the query using convert to varbinary – it’s very easy. Example:

Select * from your_table where convert(varbinary, your_column) = convert(varbinary, 'aBcD') 

USE BINARY_CHECKSUM

SELECT FROM Users WHERE BINARY_CHECKSUM(Username) = BINARY_CHECKSUM(@Username) AND BINARY_CHECKSUM(Password) = BINARY_CHECKSUM(@Password) 

use HASHBYTES

declare @first_value nvarchar(1) = 'a' declare @second_value navarchar(1) = 'A' if HASHBYTES('SHA1',@first_value) = HASHBYTES('SHA1',@second_value) begin print 'equal' end else begin print 'not equal' end -- output: -- not equal 

...in where clause

declare @example table (ValueA nvarchar(1), ValueB nvarchar(1)) insert into @example (ValueA, ValueB) values ('a', 'A'), ('a', 'a'), ('a', 'b') select ValueA + ' = ' + ValueB from @example where hashbytes('SHA1', ValueA) = hashbytes('SHA1', ValueB) -- output: -- a = a select ValueA + ' <> ' + ValueB from @example where hashbytes('SHA1', ValueA) <> hashbytes('SHA1', ValueB) -- output: -- a <> A -- a <> b 

or to find a value

declare @value_b nvarchar(1) = 'A' select ValueB + ' = ' + @value_b from @example where hashbytes('SHA1', ValueB) = hasbytes('SHA1', @value_b) -- output: -- A = A 

use Latin1_General_CS as your collation in your sql db

In MySQL if You don't want to change the collation and want to perform case sensitive search then just use binary keyword like this:

SELECT * FROM table_name WHERE binary username=@search_parameter and binary password=@search_parameter 

select * from incidentsnew1 where BINARY_CHECKSUM(CloseBy) = BINARY_CHECKSUM(Upper(CloseBy)) 

You can do by simply altering column collation as

Alter Table UserMaster Alter Column Password varchar(50) COLLATE SQL_Latin1_General_CP1_CS_AS 

If you are interested in Entity Framework approarch:

var customers = context.Customers .Where(c => EF.Functions.Collate(c.Name, "SQL_Latin1_General_CP1_CS_AS") == "John") .ToList(); 

See:

Just as others said, you can perform a case sensitive search. Or just change the collation format of a specified column as me. For the User/Password columns in my database I change them to collation through the following command:

ALTER TABLE `UserAuthentication` CHANGE `Password` `Password` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL;