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

Question

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?

Answer 1

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     OrderID = @OrderID COLLATE SQL_Latin1_General_CP1_CS_AS

Or, change the columns to be case sensitive.

You can also apply COLLATE to LIKE to make it case sensitive - e.g.,

SELECT    *
FROM     tbl_Partners
WHERE    PartnerName COLLATE SQL_Latin1_General_CP1_CS_AS LIKE 'MyEx%' COLLATE SQL_Latin1_General_CP1_CS_AS

Answer 2

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 

Answer 3

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') 

Answer 4

USE BINARY_CHECKSUM

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

Answer 5

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

Answer 6

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

Answer 7

use Latin1_General_CS as your collation in your sql db

Answer 8

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

Answer 9

You can do by simply altering column collation as

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

Answer 10

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: https://learn.microsoft.com/en-us/ef/core/miscellaneous/collations-and-case-sensitivity#explicit-collation-in-a-query

Answer 11

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;