sql-server - SQL Server PATINDEX 有时会返回错误的已找到索引

Question

我注意到 SQL Server 上的 PATINDEX(我使用的是 2016)给出了奇怪的结果。我怀疑这与排序规则和字符集有关。

我正在尝试使用 PATINDEX 获取第一个空格或连字符的索引。

在具有默认排序规则 SQL_Latin1_General_CP1_CI_AS 的数据库上，我得到的预期结果为 0(未找到):

select PATINDEX('%[ -]%',  'ABC') -- returns 0
select PATINDEX('%[ -]%', N'ABC') -- returns 0
select PATINDEX('%[- ]%', N'ABC') -- returns 0

但是，在具有排序规则 Latin1_General_100_BIN 的数据库上，我得到意外且错误的结果，表明在索引 1 处找到了空格或连字符:

select PATINDEX('%[ -]%',  'ABC') -- returns 0
select PATINDEX('%[ -]%', N'ABC') -- returns 1 (WRONG!)
select PATINDEX('%[- ]%', N'ABC') -- returns 0

总之，我在以下情况下注意到这个明显错误的结果:

• 排序规则 Latin1_General_100_BIN
• 搜索 unicode 字符串
• 模式中的连字符最后出现。

我见过其他类似的问题，但它们没有解决完全相同的情况，特别是为什么模式适用于一种排序规则而不是其他排序规则，并且适用于非 unicode 字符串而不适用于 unicode 字符串。我见过patindex t-sql special characters其中表示除第一个位置之外的任何位置的“-”字符都是 LIKE 和 PATINDEX 的范围规范(尽管我在 SQL Server PATINDEX 或 Wildcard 文档中没有看到它)。仍然没有解释为什么它在某些配置中有效，而在其他配置中无效，如图所示。

为什么 PATINDEX 如此不同并且结果明显错误？

Answer 1

您已经提到过，除了第一个位置之外的任何位置的“-”字符都是(或者更确切地说可以)范围规范。 ' -' 的问题是没有给出范围的结尾。因此，让我们找出范围的结尾是什么:

SELECT  SV.number, NCHAR(SV.number) TestChar
FROM    master..spt_values AS SV
WHERE   SV.type = 'p'
    AND NCHAR(SV.number) LIKE N'%[ -]%'

这将返回:

+--------+----------+
| number | TestChar |
+--------+----------+
|     32 |          |
|     45 |        - |
+--------+----------+

因此，在非二进制排序规则中(我使用的是 Latin1_General_CI_AS)，- 根本不被解释为范围说明符，而是被解释为文字字符。否则，也会返回 32 到 45 之间的字符。因此，只有空格和破折号会返回 patindex <> 0。让我们尝试二进制排序规则:

SELECT  SV.number, NCHAR(SV.number) TestChar 
FROM    master..spt_values AS SV
WHERE   SV.type = 'p'
    AND NCHAR(SV.number) LIKE N'%[ -]%' COLLATE Latin1_General_100_BIN2

这将返回:

+--------+----------+
| number | TestChar |
+--------+----------+
|     32 |          |
|     33 | !        |
|     34 | "        |
|     35 | #        |
|     36 | $        |
|     37 | %        |
|     38 | &        |
|     39 | '        |
|     40 | (        |
|     41 | )        |
|     42 | *        |
|     43 | +        |
|     44 | ,        |
|     45 | -        |
|     46 | .        |
|     47 | /        |
|     48 | 0        |
|     49 | 1        |
|     50 | 2        |
|     51 | 3        |
|     52 | 4        |
|     53 | 5        |
|     54 | 6        |
|     55 | 7        |
|     56 | 8        |
|     57 | 9        |
|     58 | :        |
|     59 | ;        |
|     60 | <        |
|     61 | =        |
|     62 | >        |
|     63 | ?        |
|     64 | @        |
|     65 | A        |
|     66 | B        |
|     67 | C        |
|     68 | D        |
|     69 | E        |
|     70 | F        |
|     71 | G        |
|     72 | H        |
|     73 | I        |
|     74 | J        |
|     75 | K        |
|     76 | L        |
|     77 | M        |
|     78 | N        |
|     79 | O        |
|     80 | P        |
|     81 | Q        |
|     82 | R        |
|     83 | S        |
|     84 | T        |
|     85 | U        |
|     86 | V        |
|     87 | W        |
|     88 | X        |
|     89 | Y        |
|     90 | Z        |
|     91 | [        |
|     92 | \        |
|     93 | ]        |
+--------+----------+

所以现在它被解释为一个范围，并且该范围包括A-Z。请注意，它不包含a-z!当我们使用 LIKE N'%[ -z]%' 时，将包含小写字母。在二进制中，范围的结尾(未指定时)始终为 ]，无论范围的开头是什么。

现在，让我们看看非 unicode 值的作用:

SELECT  SV.number, CHAR(SV.number) TestChar
FROM    master..spt_values AS SV
WHERE   SV.type = 'p'
    AND CHAR(SV.number) LIKE '%[ -]%' COLLATE Latin1_General_100_BIN2

这将返回:

+--------+----------+
| number | TestChar |
+--------+----------+
|     32 |          |
|     45 |        - |
+--------+----------+

因此，作为 ASCII，破折号不会被解释为范围运算符。很奇怪吧？

顺便说一句，如果您确实想搜索空格破折号，您还可以使用PATINDEX(N'% [-]%', N'ABC' COLLATE Latin1_General_BIN2).

顺便说一句:如果我们查看 Larnu 的解决方案:

SELECT  SV.number, NCHAR(SV.number) TestChar
FROM    master..spt_values AS SV
WHERE   SV.type = 'p'
    AND CHAR(SV.number) LIKE '%[ --]%' COLLATE Latin1_General_100_BIN2

你会得到:

+--------+----------+
| number | TestChar |
+--------+----------+
|     32 |          |
|     33 | !        |
|     34 | "        |
|     35 | #        |
|     36 | $        |
|     37 | %        |
|     38 | &        |
|     39 | '        |
|     40 | (        |
|     41 | )        |
|     42 | *        |
|     43 | +        |
|     44 | ,        |
|     45 | -        |
+--------+----------+

所以您仍在评估一个范围。不确定这是否是您想要的，但需要注意一些事情。