SQL Server中的LIKE操作符区分大小写吗？

Question

在有关LIKE操作符的文档中，没有提到它的大小写敏感性。那么，它是大小写敏感的吗？如何启用/禁用它？

我正在查询Microsoft SQL Server 2005安装中的varchar(n)列，如果这有影响的话。

- Marcel

---

这取决于您所使用的列（或数据库）的排序规则。如果它区分大小写，则 LIKE 也会区分大小写，如果不区分大小写，则 LIKE 也不会区分大小写。 - Lamak

请查看有关 SQL Server 排序规则的文档 http://msdn.microsoft.com/zh-cn/library/ms144250%28v=sql.105%29.aspx - GarethD

你的目标是什么？你希望它区分大小写，还是不区分大小写？ - Aaron Bertrand

大小写敏感性默认为列上的排序规则，该规则默认为数据库上的排序规则。这在大多数情况下是可以解决的，你想采取哪种方式？ - Tony Hopkinson

Answer 1

操作符不区分大小写，而是列本身区分大小写。

当安装 SQL Server 时，会选择一个默认的排序规则作为实例。除非明确指定（请查看下面的 "collate" 子句），否则创建新数据库时它会从实例继承排序规则，并且创建新列时它会从所属数据库继承排序规则。

类似于 sql_latin1_general_cp1_ci_as 这样的排序规则规定了如何处理列的内容。CI 表示不区分大小写，AS 表示区分重音符号。

可在 https://msdn.microsoft.com/zh-cn/library/ms144250(v=sql.105).aspx 上获取完整的排序规则列表。

(a) 检查实例的排序规则

select serverproperty('collation')

(b) 检查数据库排序规则

select databasepropertyex('databasename', 'collation') sqlcollation

(c) 使用不同的排序规则创建数据库

create database exampledatabase
collate sql_latin1_general_cp1_cs_as 

(d) 使用不同的排序规则创建列

create table exampletable (
    examplecolumn varchar(10) collate sql_latin1_general_cp1_ci_as null
)

(e) 修改列排序规则

alter table exampletable
alter column examplecolumn varchar(10) collate sql_latin1_general_cp1_ci_as null

更改实例和数据库排序规则是可行的，但不会影响先前创建的对象。

在需要进行字符串比较时，可以实时更改列排序规则，但在生产环境中强烈不推荐这样做，因为成本非常高昂。

select
  column1 collate sql_latin1_general_cp1_ci_as as column1
from table1

- lolol

---

似乎像 [A-Z] 这样的字符范围总是不区分大小写。然而，[ABCDEFGHIJKLMNOPQRSŠTUVWXYZŽÅÄÖ] 似乎遵循排序规则。 - jumxozizi

此外，您可以使用以下查询语句查询特定列的大小写敏感性：select COLLATION_NAME, iif(cast(COLLATIONPROPERTY(COLLATION_NAME, 'ComparisonStyle') as int) & 1 = 0, 'case sensitive', 'case insensitive') from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'exampletable' and COLUMN_NAME = 'examplecolumn'。 - Jeppe Stig Nielsen

@jumxozizi，我已经将你的建议添加到答案中了。 - John Zabroski

@JeppeStigNielsen，我已将您的建议添加到答案中。 - John Zabroski

Answer 2

所有这些关于排序的讨论似乎有点过于复杂了。为什么不使用类似以下的东西：

IF UPPER(@@VERSION) NOT LIKE '%AZURE%'

那么，无论排序规则如何，您的检查都不区分大小写。

- Phil R

---

因为这不是可搜索的（sargable）。你的示例使用了一个变量和一个前置通配符。但是对于使用不区分大小写的排序规则索引的列，like 'a%' 可以使用索引，而 upper 版本则不能。 - Martin Smith

like运算符是否区分大小写。 - jumxozizi

你需要了解排序规则，否则这样做可能是毫无意义的。例如，如果被查询的列使用 Latin1_General_CI_AS，那么执行 UPPER(@@VALUE) NOT LIKE '%SOMETHING%' 或者 @@COLUMN NOT LIKE '%SOMETHING%' 就没有意义：结果将是相同的。 - rsenna

Answer 3

如果您想进行区分大小写的搜索，而无需更改列/数据库/服务器的排序规则，您可以始终使用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;

如果您的列 / 数据库 / 服务器区分大小写，并且您不希望进行大小写敏感的搜索，那么反过来也同样适用。

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;

- Aaron Bertrand

---

请注意，在最后一个查询中，如果您使用 WHERE bar COLLATE Latin1_General_CS_AS LIKE '[j-k]%'，它将返回 John，因为在这种排序规则下，大写字母 J 位于小写字母 j 和小写字母 k 之间。就像 aAbBcC...jJkKlLmM... 一样，这并不明显。似乎 Latin1_General_BIN 在使用 LIKE 运算符进行范围搜索时更可预测。 - wqw

这是帮助了我自己的答案。 - Rob Mayo

Answer 4

在定义表时，您有一个选项来定义排序规则。如果您定义了一个区分大小写的顺序，您的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
);

这里有一个sqlfiddle上的快速演示，展示了在使用LIKE进行搜索时排序方式的结果。

- Sergey Kalinichenko

Answer 5

like运算符需要两个字符串，这些字符串必须具有兼容的排序规则，这在这里有解释。

在我看来，事情变得复杂了。以下查询会返回一个错误，说明排序规则不兼容：

select *
from INFORMATION_SCHEMA.TABLES
where 'abc' COLLATE SQL_Latin1_General_CP1_CI_AS like 'ABC' COLLATE SQL_Latin1_General_CP1_CS_AS

在这台随机的计算机上，默认的排序规则是SQL_Latin1_General_CP1_CI_AS。以下查询是成功的，但不返回任何行：

在这里，使用默认排序规则SQL_Latin1_General_CP1_CI_AS时，执行下面的查询是成功的，但没有返回任何行：

select *
from INFORMATION_SCHEMA.TABLES
where 'abc' like 'ABC' COLLATE SQL_Latin1_General_CP1_CS_AS

在区分大小写的世界中，"abc"和"ABC"这两个值是不匹配的。

换句话说，没有指定排序规则和使用默认排序规则之间存在差异。当一侧没有排序规则时，它会从另一侧“获取”一个明确的排序规则。

(如果明确的排序规则在左侧，结果也是相同的。)

- Gordon Linoff

---

您能否在一个不是系统对象（如INFORMATION_SCHEMA.TABLES）的表中重现错误？ - Aaron Bertrand

@AaronBertrand ... 是的，我可以。数据库出了问题吗？ ;) - Gordon Linoff

不好意思，我现在使用移动设备，无法启动Windows虚拟机。我只是不确定你的整个描述在技术上是否准确。 - Aaron Bertrand

Answer 6

试着运行一下，

SELECT SERVERPROPERTY('COLLATION')

然后找出您的排序规则是否区分大小写。

- RandomUs1r

Answer 7

你可以更改每个项目的属性。

- Daniel Cadena

Answer 8

在 Microsoft SQL Server Management Studio 中，您可以轻松更改排序规则。

• 右键单击表格 -> 设计。
• 选择您的列，在列属性中向下滚动到“排序规则”。
• 通过选中“区分大小写”来设置您的排序偏好。

- Anders Finn Jørgensen