SQL Server 通配符范围(例如 [A-D])如何与区分大小写的排序规则一起使用? [英] How does SQL Server Wildcard Character Range, eg [A-D], work with Case-sensitive Collation?

问题描述

谁能解释通配符范围(例如 [A-D])如何与区分大小写的归类一起使用的规则?

Can anyone explain the rules for how a wildcard character range, eg [A-D], works with a case-sensitive collation?

我会想到以下

WHERE CharColumn LIKE '[A-D]%';

将仅返回以大写字母 A、B、C 或 D 开头的记录，并排除以小写字母 a、b、c 或 d 开头的记录.

would return only records which start with an upper case A, B, C or D, and exclude records that start with a lower case a, b, c or d.

然而，实际上，它似乎返回以大写字母 A 开头的记录，但也返回以 B 或 b、C 或 c 以及 D 或 d 开头的记录.就像只有范围的第一个字符区分大小写，范围内的其余字符不区分大小写.

However, in reality, it appears to return records that start with an upper case A but also records that start with B or b, C or c and D or d. It's like only the first character of the range is case-sensitive and the remaining characters in the range are not case-sensitive.

另一方面，以下

WHERE CharColumn LIKE '[ABCD]%';

确实只返回以大写字母 A、B、C 或 D 开头的记录.但我原以为 [A-D] 将等同于 [ABCD].

does only return records which start with an upper case A, B, C or D. Yet I would have thought [A-D] would be equivalent to [ABCD].

我在 SQL Server 2005 和 SQL Server 2008 R2 中得到相同的结果.

I get the same results in SQL Server 2005 and SQL Server 2008 R2.

示例:
(插入语句使用 SQL Server 2008 行构造函数编写以保持紧凑.如果每个值都有自己的插入语句，脚本将在 SQL Server 2005 中工作)

Example:
(insert statements written with SQL Server 2008 row constructors for compactness. If each value is given its own insert statement the script will work in SQL Server 2005)

CREATE TABLE #TEST_LIKE_Patterns
    ( 
        ID INT IDENTITY(1,1),
        CharColumn VARCHAR(100) COLLATE Latin1_General_CS_AS
    );

--------------
INSERT INTO #TEST_LIKE_Patterns (CharColumn)
VALUES ('aaa'), ('aAA'), ('AAA'), ('Aaa');
--------------    
INSERT INTO #TEST_LIKE_Patterns (CharColumn)
VALUES ('bbb'), ('bBB'), ('BBB'), ('Bbb');
--------------
INSERT INTO #TEST_LIKE_Patterns (CharColumn)
VALUES ('ccc'), ('cCC'), ('CCC'), ('Ccc');
--------------    
INSERT INTO #TEST_LIKE_Patterns (CharColumn)
VALUES ('ddd'), ('dDD'), ('DDD'), ('Ddd');
--------------    
INSERT INTO #TEST_LIKE_Patterns (CharColumn)
VALUES ('eee'), ('eEE'), ('EEE'), ('Eee');
--------------    
INSERT INTO #TEST_LIKE_Patterns (CharColumn)
VALUES ('fff'), ('fFF'), ('FFF'), ('Fff');
--------------

-- Raw Data:
SELECT *
FROM #TEST_LIKE_Patterns;

SELECT *
FROM #TEST_LIKE_Patterns
WHERE CharColumn LIKE '[A-D]%';

-- Results:
/*
ID   CharColumn
--------------
3    AAA
4    Aaa
5    bbb
6    bBB
7    BBB
8    Bbb
9    ccc
10   cCC
11   CCC
12   Ccc
13   ddd
14   dDD
15   DDD
16   Ddd
*/


SELECT *
FROM #TEST_LIKE_Patterns
WHERE CharColumn LIKE '[ABCD]%';    

-- Results:
/*
ID   CharColumn
    --------------
3    AAA
4    Aaa
7    BBB
8    Bbb
11   CCC
12   Ccc
15   DDD
16   Ddd
*/

推荐答案

您需要一个二进制排序规则，如 Md. 中所示.Elias Hossain 的回答.

You need a binary collation as indicated in Md. Elias Hossain's answer.

解释是模式语法中的范围不符合排序规则排序规则.

The explanation is that ranges in the pattern syntax work off Collation sort order rules.

来自 BOL

在范围搜索中，范围内包含的字符可能会有所不同取决于排序规则的排序规则.

In range searches, the characters included in the range may vary depending on the sorting rules of the collation.

所以

;WITH T(C) AS
(
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
select 'a' union all
select 'b' union all
select 'c' union all
select 'd'
)
SELECT *
FROM T
ORDER BY C COLLATE Latin1_General_CS_AS

退货

C
----
a
A
b
B
c
C
d
D

因此范围 A-D 不包括 a 但包括 CS 归类下的其他 3 个小写字母.

So the range A-D excludes a but includes the other 3 lower case letters under a CS collation.

这篇关于SQL Server 通配符范围(例如 [A-D])如何与区分大小写的排序规则一起使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！