使用 LIKE 子句时，Microsoft SQL Server 不会返回所有内容 [英] Microsoft SQL Server does not return everything when using LIKE clause

问题描述

我在 SQL Server 2014 Express 中有一个 Products 表，其中有记录.

I have a Products table in SQL Server 2014 Express with records in it.

一些产品名称(记录)如下:

A few product names (records) are the following:

• 测试产品
• Teszt termék
• Teszt termék 2

当我执行以下查询时，一切正常:

When I execute the following query, everything works just fine:

SELECT * 
FROM Products
WHERE name LIKE 'te%'

它检索所有三个记录.但是，当我使用

It retrieves all three records. However, when I use

SELECT * 
FROM Products
WHERE name LIKE 'tes%'

被执行，只检索测试产品".当查询是

is executed, only "Test product" is retrieved. And when the query is

SELECT * 
FROM Products
WHERE name LIKE 'tesz%'

然后它再次工作，它获取所有以Tesz"开头的记录.

then it works again, it fetches all the records starting with "Tesz".

知道可能是什么问题吗?

Any idea what the problem might be?

提前感谢您的帮助.

推荐答案

我下载了你的备份.

数据库排序规则设置为Hungarian_CI_AS.

The database collation is set to Hungarian_CI_AS.

在匈牙利语中，Sz 本身被视为一个字母(三十秒匈牙利字母表中的字母).如果您不想要这种行为，那么您需要避免匈牙利语排序规则，它应该可以工作.(我测试了所有名称以 CI_AS 结尾的 SQL Server 2014 排序规则，唯一显示行为的是 Hungarian_CI_AS、Hungarian_100_CI_AS、SQL_Hungarian_CP1250_CI_AS)

Sz is treated as a letter in its own right in Hungarian (the thirty-second letter of the Hungarian alphabet). If you do not want this behaviour then you need to avoid Hungarian collations and it should work. (I tested all SQL Server 2014 collations with names ending CI_AS and the only ones showing the behaviour are Hungarian_CI_AS, Hungarian_100_CI_AS, SQL_Hungarian_CP1250_CI_AS)

您可以通过更改列排序规则(首选)或通过向查询添加显式 COLLATE 子句来执行此操作(效率较低并且会阻止查询，例如 WHERE name LIKE 'tes%' 正在通过索引查找解析).

You can do this either by altering the column collation (preferable) or by adding an explicit COLLATE clause to the query (less efficient and will block queries such as WHERE name LIKE 'tes%' being resolved through an index seek).

您也可以考虑更改数据库排序规则，但这不会级联到在旧排序规则上创建的预先存在的表列.

You might also consider changing the database collation but this will not cascade through to pre-existing table columns created on the old collation.

这篇关于使用 LIKE 子句时，Microsoft SQL Server 不会返回所有内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！