sql - ms sql 查找 unicode 0

Question

在数据库中写入了一个 unicode 0，然后开始查询以查找该值。示例:

select *,Len(test) as length, Unicode(test) as unicode,DATALENGTH(test) as datalength 
from (values (NCHAR(0x0))) as t(test)
where test like N'_'

但是没有返回值。为什么？我需要做什么才能让它起作用？

Answer 1

这是 Pattern matching documentation 的预期行为:

0x0000 (char(0)) is an undefined character in Windows collations and cannot be included in LIKE.

但是请注意，LIKE CHAR(0) 确实有效

DECLARE @Character nchar(1);
SET @Character = NCHAR(0);
SELECT CASE WHEN @Character LIKE CHAR(0) THEN 1 ELSE 0 END;

或者

DECLARE @Character nchar(10);
SET @Character = NCHAR(0);
SELECT CASE WHEN @Character LIKE '%' + CHAR(0) + '%' THEN 1 ELSE 0 END;

具体来说，文档说使用通配符，例如 '_' 对 (N)CHAR(0) 将不起作用。