具有区分大小写的排序规则的范围通配符模式匹配行为 [英] Range wildcard pattern matching behaviour with case-sensitive collations

问题描述

使用PATINDEX和区分大小写的排序规则来搜索字符串中的大写字母，我注意到这没有产生预期的结果：

Using PATINDEX and a case-sensitive collation to search for upper-case letters in a string I noticed this was not yielding the desired result:

-- returns 1
SELECT PATINDEX('%[A-Z]%'
                    , 'abCde' COLLATE SQL_Latin1_General_Cp1_CS_AS); 

但是，指定每个字母AZ都会：

however, specifying every letter, A-Z, does:

-- returns 3
SELECT PATINDEX('%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%'
                    , 'abCde' COLLATE SQL_Latin1_General_Cp1_CS_AS); 

我对第一种情况下使用范围的理解是否正确？为什么这样的行为？

Is my understanding of using a range in the first case incorrect? Why is the behaviour like this?

推荐答案

不幸的是，范围运算符有点可笑。 AZ的字母范围是：

Unfortunately, the range operators are a bit funny. The range of letters from A-Z is:

AbBcCdDeE...yYzZ

也就是说，小写字母紧跟大写字母。这也很有趣，因为如果要同时处理大小写字符，则在区分大小写的排序规则中，范围AZ排除小写字母a。

That is, lower case characters immediately precede their upper case counterpart. This is also fun because if you want to deal with both upper and lower case characters, in a case sensitive collation, the range A-Z excludes lower case a.

关于范围扩展的方式，我应该说以上是基于我通常使用的排序规则。范围的实际扩展方式是取决于排序规则。 如果，您可以找到一个排序规则，例如，所有大写字符都出现在所有小写字符之前，那么该范围将按预期工作。 （可能是二进制排序规则之一？）

I should say the above, regarding how the range expands out, is based on the collations I generally work with. How the range actually expands is collation dependent. If you can find a collation where, for instance, all upper case characters occur before all lower case characters, then the range would work as you expect. (Possibly one of the binary collations?)

这篇关于具有区分大小写的排序规则的范围通配符模式匹配行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！