SQL“赞"运算符和"aa" [英] SQL 'Like' operator and 'aa'

问题描述

我们在一个应用程序中偶然发现了一个非常奇怪的问题.搜寻引擎使用存储过程来比较一堆过滤器.但是，当插入特定类型的字符串时，SQL Server(2005)的行为非常奇怪.我已将问题归结为以下问题:

We've stumbled upon a very odd problem in one of our applications. The seach engine uses a stored procedure to compare a bunch of filters. However when a specific type of string is inserted, the sql server (2005) behaves very odd. I've isolated the problem to the following:

select 'match!' where  'teliaa' like '%telia%'

排序规则是Danish Norwegian CI AS，并且我们的字符含义相同.这包括"aa"，也表示å".

The collation is Danish Norwegian CI AS and we have characters that mean the same thing. This includes 'aa' which also means 'å'.

任何人都可以解释为什么上面的语句不产生匹配！"

Can anyone explain why the above statement does not yield 'match!'

推荐答案

排序规则不会自动将"aa"匹配为å".

The collation won't automatically match "aa" to "å".

这将确保å"已正确排序以及其他一些内容，但它不会替代. 例如，德语中的"ss"和ß"也是如此.

It will make sure that "å" is sorted correctly and some other stuff but it won't substitute. The same applies "ss" vs "ß" in German, for example

您必须以一种或另一种方式清除数据.

You'd have to clean the data one way or the other.

SELECT REPLACE ('teliå', 'å', 'aa'), /* ...or  */REPLACE ('teliaa', 'aa', 'å')

编辑，2013年5月

我猜想å在该排序规则中与aa不匹配. 但是它确实可以正确排序

I'm guessing å does not match aa in that collation. However it does sort correctly

DECLARE @foo TABLE (bar varchar(2))
INSERT @foo VALUES ('Ab'),('Aa'),('aa'), ('å'), ('Za');

SELECT * FROM @foo ORDER BY bar COLLATE Danish_Norwegian_CI_AS;

SELECT * FROM @foo WHERE bar COLLATE Danish_Norwegian_CI_AS = 'Aa';
SELECT * FROM @foo WHERE bar COLLATE Danish_Norwegian_CI_AS = 'a';
SELECT * FROM @foo WHERE bar COLLATE Danish_Norwegian_CI_AS = 'å';

这篇关于SQL“赞"运算符和"aa"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！