需要区分大小写的排序规则，其中ss！=ß [英] Need a case insensitive collation where ss != ß

问题描述

对于在SQL Server Express 2012上运行的数据库中的特定列，我需要一个排序规则，其中比较字符串时ss和ß不会被视为相同。还要分别将ä和ae，ö和oe以及ü和ue视为不同。 Latin1_General_CI_AS提供了后者，但不区分ss和ß。也就是说， WHERE ThatColumn ='Fass'会同时产生 Fass 和Faß。

For a specific column in a database running on SQL Server Express 2012 I need a collation where ss and ß are not considered the same when comparing strings. Also ä and ae, ö and oe and ü and ue should be considered different respectively. Latin1_General_CI_AS provides the latter, but ss and ß are not distinguished. That is, WHERE ThatColumn = 'Fass' would yield both Fass and Faß.

我只是坚持使用BIN / BIN2，但是我需要不区分大小写。如果没有其他效果，则必须使用Latin1_General_BIN / Latin1_General_BIN2并确保所有内容都是大写或小写。

I would simply stick to BIN/BIN2, but I need case insensitivity. If nothing else works I'll have to use Latin1_General_BIN/Latin1_General_BIN2 and make sure everything is uppercase or lowercase myself. It would mean more work as I need to be able to retrieve the version with proper casing as well.

但是如果有可以满足我需要的排序规则，请让我来完成。知道。

But if there's a collation that does what I need, please let me know. Thanks in advance!

更新：
有关要求的更多信息：数据库包含旧系统中的个人名称，该旧系统仅支持ASCII字符。也就是说，像Müller和Faß这样的名称都存储为Mueller和Fass。在新系统中，用户将具有重命名这些人的功能，例如将 Mueller重命名为Müller。为了找到需要重命名的实体，我需要搜索包含例如 Fass。但由于现在查询也返回Faß，这不是我想要的。我仍然需要/不区分大小写，因为用户应该能够搜索 fass并仍然获得 Fass。

Update: More information about the requirements: the database contains personal names from a legacy system which only supported ASCII characters. That is, names like Müller and Faß are stored as Mueller and Fass. In the new system the user will have a function to rename those persons, e.g. rename "Mueller" to "Müller". To find the entities that need renaming I need to search for rows containing e.g. "Fass". But as it is now the query also returns "Faß", which is not what I want. I still need/want case insensitivity as the user should be able to search for "fass" and still get "Fass".

系统还有更多功能，但我可以肯定地说，我需要区分ss和ß，ä和ae等。

There's more to the system, but I can definitively say that I need to distinguish between ss and ß, ä and ae etc.

推荐答案

排序规则SQL_Latin1_General_CP1_CI_AS认为'ss'与'ß'不同，因此它可能对您有用。这是一个传统的归类，因此可能会与操作系统和应用程序平台产生不兼容性。

The collation SQL_Latin1_General_CP1_CI_AS considers 'ss' to be different from 'ß', so it may work for you. It's a legacy collation, so it might create incompatibilities with operating systems and application platforms. It might also have other quirks I don't know of.

几年前，我为类似的问题设计了一个变通的解决方法，您可以看看此处。 （单击替代方法（1）选项卡。）在这种情况下，问题在于有关键列的唯一性，而不是字符串比较结果，因此应将其应用于您的情况（并比较两列以进行单个字符串比较）可能不可行。

A few years ago, I sketched out a kludgy workaround for a similar problem, and you can take a look at it here. (Click on the "Workarounds (1)" tab.) The problem in that case was regarding uniqueness in a key column, not string comparison results, so to apply it in your situation (and compare two columns to do a single string comparison) might be unfeasible.

这篇关于需要区分大小写的排序规则，其中ss！=ß的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！