SQL Server中PATINDEX处理VARCHAR与NVARCHAR脱字符^的差异疑问

我在理解SQL Server中PATINDEX函数匹配脱字符^的行为时遇到困难，不管是使用它的排除功能，还是匹配^本身都有问题。尝试了匹配模式与输入参数为VARCHAR和NVARCHAR的不同组合，但结果不符合预期，也没找到有效的参考信息。

测试环境 #

SQL Server 2019，排序规则为SQL_Latin1_General_CP1_CI_AS

测试代码 #

-- 使用patindex匹配脱字符和非脱字符的结果令我困惑。
-- 我预期无匹配，所有结果应为0，但只有第一个字段返回0，其余返回1（找到匹配）。
SELECT
   'match_^__v_v'     = PATINDEX(  '^[^^]%',  '^^Z' COLLATE Latin1_GENERAL_CI_AS)
  ,'match_^__v_nv'    = PATINDEX(  '^[^^]%', N'^^Z' COLLATE Latin1_GENERAL_CI_AS)
  ,'match_^__nv_nv'   = PATINDEX( N'^[^^]%', N'^^Z' COLLATE Latin1_GENERAL_CI_AS)
  ,'match_^__nv_v'    = PATINDEX( N'^[^^]%',  '^^Z' COLLATE Latin1_GENERAL_CI_AS)

-- 尝试添加额外方括号后仍存在相同问题，结果与上述一致：
SELECT
   'match_[^]__v_v'   = PATINDEX(  '[^][^^]%',  '^^Z' COLLATE Latin1_GENERAL_CI_AS)
  ,'match_[^]__v_nv'  = PATINDEX(  '[^][^^]%', N'^^Z' COLLATE Latin1_GENERAL_CI_AS)
  ,'match_[^]__nv_nv' = PATINDEX( N'[^][^^]%', N'^^Z' COLLATE Latin1_GENERAL_CI_AS)
  ,'match_[^]__nv_v'  = PATINDEX( N'[^][^^]%',  '^^Z' COLLATE Latin1_GENERAL_CI_AS)

-- 对普通字母执行类似操作无此问题，所有字段均返回0，符合预期。
SELECT
   'match_a__v_v'     = PATINDEX(  'a[^a]%',  'aaZ' COLLATE Latin1_GENERAL_CI_AS)
  ,'match_a__v_nv'    = PATINDEX(  'a[^a]%', N'aaZ' COLLATE Latin1_GENERAL_CI_AS)
  ,'match_a__nv_nv'   = PATINDEX( N'a[^a]%', N'aaZ' COLLATE Latin1_GENERAL_CI_AS)
  ,'match_a__nv_v'    = PATINDEX( N'a[^a]%',  'aaZ' COLLATE Latin1_GENERAL_CI_AS)

具体疑问 #

• 按预期应无匹配，所有字段应返回0，为何结果不符合预期？
• 为何PATINDEX对VARCHAR和NVARCHAR的行为存在差异？
• 应在何种场景下使用VARCHAR或NVARCHAR？是否只需保证匹配模式与输入类型一致？

显然这与脱字符的特殊功能有关，我忽略了什么？

在了解排序规则与数据类型的关联后，添加COLLATE Latin1_GENERAL_CI_AS后，所有涉及脱字符的公式均出现相同异常。

可行解决方法 #

使用二进制排序规则按ASCII码精确匹配，并在方括号内定义首尾相同的范围（含义为「不包含^到之间的字符」），执行后返回预期的0值：

SELECT "match_nv_nv" = PATINDEX( N'^[^^-^]%', N'^^Z' COLLATE Latin1_General_BIN2)

内容的提问来源于stack exchange，提问作者Der U