SQL Server中使用LIKE匹配NVARCHAR非标准字符的异常问题

使用SQL Server版本15.0.4410.1，排序规则为Latin1_General_CI_AS，执行以下测试代码时出现不符合预期的结果：

原始测试代码 #

DECLARE @a NVARCHAR(200)
SET @a = N'a' + NCHAR(0x001F) + N'b' 

SELECT @a, CONVERT(VARBINARY(MAX), @a) as AsBytes

-- run tests 1 to 4:
IF @a =   N'a' + NCHAR(0x001F) + N'b' 
    SELECT '1: yes'
ELSE
    SELECT '1: no'

IF @a =   N'a' + NCHAR(0x1F00) + N'b' 
    SELECT '2: yes'
ELSE
    SELECT '2: no'

IF @a LIKE  N'%' + NCHAR(0x001F) + N'%' 
    SELECT '3: yes'
ELSE
    SELECT '3: no'

IF @a LIKE N'%' + NCHAR(0x1F00) + N'%' 
    SELECT '4: yes'
ELSE
    SELECT '4: no'

执行结果 #

ab 0x61001F006200
1: yes
2: no
3: yes
4: yes

预期测试4返回4: no，但实际返回4: yes，该现象的原因可通过以下补充测试和分析说明：

补充验证测试代码 #

IF N'az' LIKE N'a' + NCHAR(0x1F00) + N'z'
    SELECT '5: yes'
ELSE
    SELECT '5: no'    

IF N'az' collate Latin1_General_BIN2 LIKE N'a' + NCHAR(0x1F00) + N'z'  
    SELECT '6: yes'
ELSE
    SELECT '6: no'

补充测试结果 #

5: yes
6: no

原因分析 #

1. 非二进制排序规则的字符处理逻辑：Latin1_General_CI_AS属于字典类排序规则，这类规则会对Unicode字符进行归一化处理，忽略部分控制字符、不可打印字符或被判定为“无意义”的字符。NCHAR(0x1F00)属于Unicode未定义/控制字符范畴，在该排序规则下的LIKE匹配中，它会被视为可跳过的占位，导致N'a' + NCHAR(0x1F00) + N'z'等价于N'az'进行匹配，因此测试5返回yes，测试4中@a的a+0x001F+b结构也能被%+0x1F00+%匹配到。
2. 二进制排序规则的精确匹配特性：当指定Latin1_General_BIN2这类二进制排序规则时，SQL Server会严格按照字符的二进制编码进行比较，不会对字符做任何归一化或忽略处理，因此能准确区分N'az'和N'a' + NCHAR(0x1F00) + N'z'，测试6返回预期的no。

结论 #

如果需要对包含特殊Unicode字符的字符串进行精确LIKE匹配，必须显式指定二进制排序规则，避免非二进制排序规则的字符处理逻辑干扰匹配结果。

内容的提问来源于stack exchange，提问作者Moe Sisko