SQL Server Collat​​ion匹配Java不区分大小写的Unicode比较

I've been reading a lot about collations and have tried a few different things, but I can't seem to get the results I want.

我一直在阅读很多关于校对的内容，并尝试了一些不同的东西，但我似乎无法得到我想要的结果。

I'm looking for a collation that is case insensitive, accent sensitive, but places all characters with accents after all the "regular" characters. For example, I want a result like this:

我正在寻找一个不区分大小写，重音敏感的排序规则，但是在所有“常规”字符之后将所有字符都加上重音符号。例如，我想要一个这样的结果：

• "a.O. Ark"
• “a.O. Ark”
• "A.O. Smith Electrical Products Company"
• “A.O.史密斯电气产品公司”
• "A.Z. Vesalius"
• “A.Z. Vesalius”
• "A.ö.BKH St. Johann/ Tirol"
• “A.ö.BKHSt。Johann / Tirol”

Latin1_General_BIN treats accents the way I want it, but it's case sensitive.

Latin1_General_BIN以我想要的方式处理重音，但它区分大小写。

Latin1_General_CI_AS, SQL_Latin1_General_CI_AS, and Latin1_General_CI_AI all return this:

Latin1_General_CI_AS，SQL_Latin1_General_CI_AS和Latin1_General_CI_AI都返回：

• "a.O. Ark"
• “a.O. Ark”
• "A.O. Smith Electrical Products Company"
• “A.O.史密斯电气产品公司”
• "A.ö.BKH St. Johann/ Tirol"
• “A.ö.BKHSt。Johann / Tirol”
• "A.Z. Vesalius"
• “A.Z. Vesalius”

Is there such a collation?

有没有这样的整理？

1 个解决方案

#1

The simple answer to this question is "No" - there is no collation that orders characters the way you want.

这个问题的简单答案是“不” - 没有按照您想要的方式对字符进行排序的排序规则。

A collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish, or an alphabet, such as Latin1_General (the Latin alphabet used by western European languages).

排序规则编码管理正确使用字符（如希腊语或波兰语）或字母表（如Latin1_General（西欧语言使用的拉丁字母））的字符的规则。

What you want is not unreasonable, but it does not reflect any language or alphabet that has been modeled by a current SQL Server collation.

你想要的并不是不合理的，但它并不反映任何由当前SQL Server排序规则建模的语言或字母。

As noted by @dasblinkenlight you could ORDER BY (UPPER(str) collate Latin1_General_BIN) to get the order you describe.

正如@dasblinkenlight所指出的，您可以通过ORDER BY（UPPER（str）整理Latin1_General_BIN）来获得您描述的顺序。