SQL MSSQL Server 的 LIKE 运算符是否区分大小写？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14962419/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is the LIKE operator case-sensitive with MSSQL Server?
提问by Marcel
In the documentation about the LIKE operator, nothing is told about the case-sensitivity of it. Is it? How to enable/disable it?
在关于 LIKE 运算符的文档中，没有说明它的大小写敏感度。是吗？如何启用/禁用它？
I am querying varchar(n)columns, on an Microsoft SQL Server 2005 installation, if that matters.
varchar(n)如果这很重要，我正在查询Microsoft SQL Server 2005 安装上的列。
回答by lolol
It is not the operator that is case sensitive, it is the column itself.
区分大小写的不是运算符，而是列本身。
When a SQL Server installation is performed a default collation is chosen to the instance. Unless explicitly mentioned otherwise (check the collate clause bellow) when a new database is created it inherits the collation from the instance and when a new column is created it inherits the collation from the database it belongs.
执行 SQL Server 安装时，会为实例选择默认排序规则。除非另有明确说明（检查下面的 collate 子句），否则在创建新数据库时，它会从实例继承排序规则，而在创建新列时，它会从它所属的数据库继承排序规则。
A collation like sql_latin1_general_cp1_ci_asdictates how the content of the column should be treated. CI stands for case insensitive and AS stands for accent sensitive.
类似的排序规则sql_latin1_general_cp1_ci_as指示应如何处理列的内容。CI 代表不区分大小写，AS 代表区分重音。
A complete list of collations is available at https://msdn.microsoft.com/en-us/library/ms144250(v=sql.105).aspx
https://msdn.microsoft.com/en-us/library/ms144250(v=sql.105).aspx上提供了完整的排序规则列表
(a) To check a instance collation
(a) 检查实例归类
select serverproperty('collation')
(b) To check a database collation
(b) 检查数据库排序规则
select databasepropertyex('databasename', 'collation') sqlcollation
(c) To create a database using a different collation
(c) 使用不同的排序规则创建数据库
create database exampledatabase
collate sql_latin1_general_cp1_cs_as
(d) To create a column using a different collation
(d) 使用不同的排序规则创建列
create table exampletable (
examplecolumn varchar(10) collate sql_latin1_general_cp1_ci_as null
)
(e) To modify a column collation
(e) 修改列排序规则
alter table exampletable
alter column examplecolumn varchar(10) collate sql_latin1_general_cp1_ci_as null
It is possible to change a instance and database collations but it does not affect previously created objects.
可以更改实例和数据库排序规则，但不会影响以前创建的对象。
It is also possible to change a column collation on the fly for string comparison, but this is highly unrecommended in a production environment because it is extremely costly.
也可以动态更改列排序规则以进行字符串比较，但在生产环境中极不推荐这样做，因为它非常昂贵。
select
column1 collate sql_latin1_general_cp1_ci_as as column1
from table1
回答by Phil R
All this talk about collation seem a bit over-complicated. Why not just use something like:
所有这些关于整理的讨论似乎有点过于复杂。为什么不使用类似的东西：
IF UPPER(@@VERSION) NOT LIKE '%AZURE%'
Then your check is case insensitive whatever the collation
那么无论排序规则如何，您的支票都不区分大小写
回答by dasblinkenlight
You have an option to define collation orderat the time of defining your table. If you define a case-sensitive order, your LIKEoperator will behave in a case-sensitive way; if you define a case-insensitive collation order, the LIKEoperator will ignore character case as well:
您可以选择在定义表时定义整理顺序。如果您定义区分大小写的订单，您的LIKE操作员将以区分大小写的方式运行；如果你定义了一个不区分大小写的排序顺序，LIKE操作符也会忽略字符大小写：
CREATE TABLE Test (
CI_Str VARCHAR(15) COLLATE Latin1_General_CI_AS -- Case-insensitive
, CS_Str VARCHAR(15) COLLATE Latin1_General_CS_AS -- Case-sensitive
);
Here is a quick demo on sqlfiddleshowing the results of collation order on searches with LIKE.
这是一个关于 sqlfiddle的快速演示，显示了使用LIKE.
回答by Aaron Bertrand
If you want to achieve a case sensitive search without changing the collation of the column / database / server, you can always use the COLLATEclause, e.g.
如果你想在不改变列/数据库/服务器的排序规则的情况下实现区分大小写的搜索，你总是可以使用COLLATE子句，例如
USE tempdb;
GO
CREATE TABLE dbo.foo(bar VARCHAR(32) COLLATE Latin1_General_CS_AS);
GO
INSERT dbo.foo VALUES('John'),('john');
GO
SELECT bar FROM dbo.foo
WHERE bar LIKE 'j%';
-- 1 row
SELECT bar FROM dbo.foo
WHERE bar COLLATE Latin1_General_CI_AS LIKE 'j%';
-- 2 rows
GO
DROP TABLE dbo.foo;
Works the other way, too, if your column / database / server is case sensitive and you don't want a case sensitive search, e.g.
如果您的列/数据库/服务器区分大小写并且您不想要区分大小写的搜索，则也可以以另一种方式工作，例如
USE tempdb;
GO
CREATE TABLE dbo.foo(bar VARCHAR(32) COLLATE Latin1_General_CI_AS);
GO
INSERT dbo.foo VALUES('John'),('john');
GO
SELECT bar FROM dbo.foo
WHERE bar LIKE 'j%';
-- 2 rows
SELECT bar FROM dbo.foo
WHERE bar COLLATE Latin1_General_CS_AS LIKE 'j%';
-- 1 row
GO
DROP TABLE dbo.foo;
回答by Gordon Linoff
The likeoperator takes two strings. These strings have to have compatible collations, which is explained here.
该like操作需要两个字符串。这些字符串必须具有兼容的排序规则，这在此处进行了解释。
In my opinion, things then get complicated. The following query returns an error saying that the collations are incompatible:
在我看来，事情会变得复杂。以下查询返回一个错误，指出排序规则不兼容：
select *
from INFORMATION_SCHEMA.TABLES
where 'abc' COLLATE SQL_Latin1_General_CP1_CI_AS like 'ABC' COLLATE SQL_Latin1_General_CP1_CS_AS
On a random machine here, the default collation is SQL_Latin1_General_CP1_CI_AS. The following query is successful, but returns no rows:
在此处的随机机器上，默认排序规则是SQL_Latin1_General_CP1_CI_AS. 以下查询成功，但不返回任何行：
select *
from INFORMATION_SCHEMA.TABLES
where 'abc' like 'ABC' COLLATE SQL_Latin1_General_CP1_CS_AS
The values "abc" and "ABC" do not match in a case-sensitve world.
在区分大小写的世界中，值“abc”和“ABC”不匹配。
In other words, there is a difference between having no collation and using the default collation. When one side has no collation, then it is "assigned" an explicit collation from the other side.
换句话说，没有排序规则和使用默认排序规则是有区别的。当一侧没有排序规则时，则从另一侧“分配”一个显式排序规则。
(The results are the same when the explicit collation is on the left.)
（当显式排序规则位于左侧时，结果相同。）
回答by RandomUs1r
Try running,
尝试跑步，
SELECT SERVERPROPERTY('COLLATION')
Then find out if your collation is case sensitive or not.
然后找出您的排序规则是否区分大小写。
回答by Daniel Cadena
You can change from the property of every item.
您可以更改每个项目的属性。
回答by Anders Finn J?rgensen
You can easy change collation in Microsoft SQL Server Management studio.
您可以在 Microsoft SQL Server Management Studio 中轻松更改排序规则。
- right click table -> design.
- choose your column, scroll down i column properties to Collation.
- Set your sort preference by check "Case Sensitive"
- 右键单击表-> 设计。
- 选择您的列，向下滚动 i 列属性到排序规则。
- 通过选中“区分大小写”来设置您的排序偏好
