如何在 WHERE 子句中进行区分大小写的搜索（我使用的是 SQL Server）？

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

提问by Veera

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.

我想在我的 SQL 查询中进行区分大小写的搜索。但默认情况下，SQL Server 不考虑字符串的情况。

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

关于如何在 SQL 查询中进行区分大小写的搜索的任何想法？

回答by Ashish Jain

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

可以通过更改 Collat​​ion来完成。默认情况下，它不区分大小写。

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 Jonas Lincoln

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 

回答by Juan Carlos Velez

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

您可以使用 convert to varbinary 进行查询 - 这非常简单。例子：

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

回答by Sandeep

USE BINARY_CHECKSUM

使用 BINARY_CHECKSUM

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

回答by Dub

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

...在 where 子句中

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

回答by blake

use Latin1_General_CS as your collation in your sql db

在 sql db 中使用 Latin1_General_CS 作为排序规则

回答by Sumit Joshi

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

在 MySQL 中，如果您不想更改排序规则并希望执行区分大小写的搜索，则只需使用二进制关键字，如下所示：

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

回答by Hemant yadav

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

回答by Gfast2

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;