MSSQL Server 的 LIKE 运算符是否区分大小写? [英] Is the LIKE operator case-sensitive with MSSQL Server?

问题描述

在关于 LIKE 运算符的文档中，没有提及它的区分大小写.是吗?如何启用/禁用它?

In the documentation about the LIKE operator, nothing is told about the case-sensitivity of it. Is it? How to enable/disable it?

我正在查询 Microsoft SQL Server 2005 安装上的 varchar(n) 列，如果这很重要的话.

I am querying varchar(n) columns, on an Microsoft SQL Server 2005 installation, if that matters.

推荐答案

区分大小写的不是运算符，而是列本身.

It is not the operator that is case sensitive, it is the column itself.

执行 SQL Server 安装时，会为实例选择默认排序规则.除非另有明确说明(检查下面的 collat​​e 子句)，否则在创建新数据库时，它会从实例继承排序规则，而在创建新列时，它会从它所属的数据库继承排序规则.

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_latin1_general_cp1_ci_as 这样的排序规则指示应如何处理列的内容.CI 代表不区分大小写，AS 代表区分重音.

A collation like sql_latin1_general_cp1_ci_as dictates how the content of the column should be treated. CI stands for case insensitive and AS stands for accent sensitive.

完整的排序规则列表可在 https://msdn.microsoft.com/en-us/library/ms144250(v=sql.105).aspx

A complete list of collations is available at https://msdn.microsoft.com/en-us/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

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

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

这篇关于MSSQL Server 的 LIKE 运算符是否区分大小写?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！