SQL Server 查询区分大小写

SQL Server queries case sensitivity

提问by Gold

I have this database:

我有这个数据库：

abcDEF

ABCdef

abcdef

if I write: select * from MyTbl where A='ABCdef'

如果我写： select * from MyTbl where A='ABCdef'

how to get: ABCdef

怎么获得： ABCdef

and how to get:

以及如何获得：

abcDEF

    ABCdef

    abcdef

Thanks in advance

提前致谢

forgot to write - sqlCE

忘了写 - sqlCE

回答by RedFilter

You can make your query case sensitive by making use of the COLLATEkeyword.

您可以使用COLLATE关键字使查询区分大小写。

SELECT A 
FROM MyTbl 
WHERE A COLLATE Latin1_General_CS_AS = 'ABCdef'

回答by gbn

If you have abcDEF, ABCdef, abcdef already in the database then it's already case sensitive or you have no constraint.

如果数据库中已经有 abcDEF、ABCdef、abcdef，那么它已经区分大小写或者您没有约束。

You'd have to add a COLLATEon both sides to make sure it's truly case sensitive (for a non case sensitive database) which will invalidate index usage

您必须COLLATE在两侧添加 a以确保它真正区分大小写（对于不区分大小写的数据库），这将使索引使用无效

SELECT TheColumn
FROM MyTable 
WHERE TheColumn COLLATE Latin1_General_CS_AS = 'ABCdef' COLLATE Latin1_General_CS_AS

What about accents too? Latin1_General_CS_AI, Latin1_General_Bin?

还有口音呢？Latin1_General_CS_AI, Latin1_General_Bin?

回答by Arun R. Prajapati

Try this just add binary keyword after where:

试试这个，只需在之后添加二进制关键字where：

select * from MyTbl where binary A = 'ABCdef';

回答by AlexanderMP

It's all about collation. Each one has a suffix (CI and CS, meaning Case Insensitive, and Case Sensitive).

这都是关于整理的。每个都有一个后缀（CI 和 CS，表示不区分大小写和区分大小写）。

http://www.databasejournal.com/features/mssql/article.php/10894_3302341_2/SQL-Server-and-Collation.htm

http://www.databasejournal.com/features/mssql/article.php/10894_3302341_2/SQL-Server-and-Collat​​ion.htm

回答by Josh Anderson

SQL is non-case-sensitive by default, so you will get all three items if doing a simple string comparison. To make it case-sensitive, you can cast the value of the field and your search value as varbinary:

SQL 默认不区分大小写，因此如果进行简单的字符串比较，您将获得所有三个项目。为了区分大小写，您可以将字段的值和搜索值转换为 varbinary：

SELECT * FROM MyTbl WHERE CAST(A AS varbinary(20)) = CAST('ABCdef' as varbinary(20))

The above assumes your varchar field is sized at 20. For nvarchar double it (thanks @ps2goat).

以上假设您的 varchar 字段的大小为 20。对于 nvarchar，将其加倍（感谢 @ps2goat）。