将 s, t 与 SQL Server 中的 ş, ţ 进行比较 [英] compare s, t with ş, ţ in SQL Server

问题描述

我关注了这篇文章如何在 SQL Server 中执行不区分重音的比较(e 与 è、é、ê 和 ë)? 但它对ş"、ţ"字符没有帮助.

I followed this post How do I perform an accent insensitive compare (e with è, é, ê and ë) in SQL Server? but it doesn't help me with " ş ", " ţ " characters.

如果城市名称为iaşi"，则不会返回任何内容:

This doesn't return anything if the city name is " iaşi " :

SELECT *
  FROM City
 WHERE Name COLLATE Latin1_general_CI_AI LIKE '%iasi%' COLLATE Latin1_general_CI_AI

如果城市名称是iaşi"，这也不会返回任何内容(注意 LIKE 模式中的外来 ş):

This also doesn't return anything if the city name is " iaşi " (notice the foreign ş in the LIKE pattern):

SELECT *
  FROM City
 WHERE Name COLLATE Latin1_general_CI_AI LIKE '%iaşi%' COLLATE Latin1_general_CI_AI

我使用的是 SQL Server Management Studio 2012.
我的数据库和列排序规则是Latin1_General_CI_AI"，列类型是 nvarchar.

I'm using SQL Server Management Studio 2012.
My database and column collation is "Latin1_General_CI_AI", column type is nvarchar.

我怎样才能让它发挥作用?

How can I make it work?

推荐答案

您指定的字符不是 Latin1 代码页的一部分，因此除了 中的序数外，它们不能以任何其他方式进行比较Latin1_General_CI_AI.事实上，我认为它们在给定的排序规则中根本不起作用.

The characters you've specified aren't part of the Latin1 codepage, so they can't ever be compared in any other way than ordinal in Latin1_General_CI_AI. In fact, I assume that they don't really work at all in the given collation.

如果您只使用一种排序规则，只需使用正确的排序规则(例如，如果您的数据是土耳其语，请使用 Turkish_CI_AI).如果您的数据来自多种不同的语言，则必须使用 unicode 和正确的排序规则.

If you're only using one collation, simply use the correct collation (for example, if your data is turkish, use Turkish_CI_AI). If your data is from many different languages, you have to use unicode, and the proper collation.

但是，还有一个问题.在罗马尼亚语或土耳其语等语言中，ş不是重音s，而是一个完全独立的字符 - 请参阅http://collat​​ion-charts.org/mssql/mssql.0418.1250.Romanian_CI_AI.html.与例如对比.š 是 s 的重音形式.

However, there's an additional issue. In languages like Romanian or Turkish, ş is not an accented s, but rather a completely separate character - see http://collation-charts.org/mssql/mssql.0418.1250.Romanian_CI_AI.html. Contrast with eg. š which is an accented form of s.

如果你真的需要 ş 等于 s，你必须手动替换原始字符.

If you really need ş to equal s, you have replace the original character manually.

此外，当您使用 unicode 列(nvarchar 和一堆)时，请确保您还使用 unicode literals，即.使用 N'%iasi%' 而不是 '%iasi%'.

Also, when you're using unicode columns (nvarchar and the bunch), make sure you're also using unicode literals, ie. use N'%iasi%' rather than '%iasi%'.

这篇关于将 s, t 与 SQL Server 中的 ş, ţ 进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！