当我使用LIKE运算子时，SQL-Server忽略了我的COLLATION [英] SQL-Server is ignoring my COLLATION when I'm using LIKE operator

问题描述

我正在使用西班牙语数据库，因此当我查找 aeiou和 aeiou时，也可以在where子句中获得áéíóú或 AEIOU或ÁÉÍÓÚ：

I'm working with Spanish database so when I'm looking for and "aeiou" I can also get "áéíóú" or "AEIOU" or "ÁÉÍÓÚ", in a where clause like this:

SELECT * FROM myTable WHERE stringData like '%perez%'

我正在体验：

* perez
* PEREZ
* Pérez
* PÉREZ

所以我将数据库更改为排序规则：Modern_Spanish_CI_AI

So I changed my database to collation: Modern_Spanish_CI_AI

我只能得到：

* perez
* PEREZ

但是如果我这样做：

SELECT * FROM myTable WHERE stringData like '%perez%' COLLATE Modern_Spanish_CI_AI 

I得到所有结果好，所以我的问题是，为什么如果我的数据库是COLLATE Modern_Spanish_CI_AI，我必须为查询设置相同的排序规则？

I get all results OK, so my question is, why if my database is COLLATE Modern_Spanish_CI_AI I have to set the same collation to my query???

我正在使用SQL -Server 2008

I'm using SQL-Server 2008

推荐答案

您需要更改表COLU的排序规则MN本身。

You need to change the collation of the table COLUMN itself.

select collation_name, *
from sys.columns
where object_id = object_id('tblname')
  and name = 'stringdata';

如果幸运的话，它很容易（例如）

If you're lucky it is as easy as (example)

alter table tblname alter column stringdata varchar(20) collate Modern_Spanish_CI_AS

但是如果您有约束和/或架构绑定的引用，它可能会变得很复杂。

处理带有混合排序规则的数据库可能非常困难，因此您可能想要重新整理所有表格列。

But if you have constraints and/or schema bound references, it can get complicated.
It can be very difficult to work with a database with mixed collations, so you may want to re-collate all the table columns.

这篇关于当我使用LIKE运算子时，SQL-Server忽略了我的COLLATION的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！