Moe Sisko
Moe Sisko

Reputation: 12051

Using Like on NVARCHAR with non-standard characters

Sample code:

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'

This returns:

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

I was expecting the last line returned to be "4: no". Can anyone explain why it returns "4 : yes" ?

SQL Server version used is 15.0.4410.1
SQL Server collation being used is Latin1_General_CI_AS.

Running additional tests:

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'

Returns:

5: yes
6: no

So may have to specify one of the binary collations when using LIKE.

Upvotes: 2

Views: 80

Answers (0)

Related Questions