Search code examples
sqlsql-serversql-like

ms sql find unicode 0

At the database is a unicode 0 written and then a query started to find the value. Example:

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

but the value is not returned. Why? What need I to do that it works?

Solution

  • This is expected behaviour per the Pattern matching documentation:

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

    Note, however, that LIKE CHAR(0) does work

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

    Or

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

    Specifically, the documentation is saying that using a wildcard, such as '_' for (N)CHAR(0) will not work.