Reputation: 12051
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