TSQL - Cannot find unicode char with CHARINDEX

Question

I cannot find a special Unicode char in a string with TSQL - CHARINDEX

declare @n nchar(1), @str nchar(20)
set @n = nchar(8237) 
select 'x'+@n+'x'
set @str  = N'43‭0'+@n+N'22‬' -- after "3" there is also an unicode 8237 char
select charindex( @n  ,@str)
select patindex( '%‭%'  ,@str) -- between %% there is an unicode 8237 char

Do you have any idea? Thanks in advance..

Answer 1

U+202D LEFT-TO-RIGHT OVERRIDE is a directional character that will be ignored by most collation comparisons. To fix that, explicitly use a binary collation:

SELECT CHARINDEX(@n, @str COLLATE Latin1_General_BIN2)

And similarly for the PATINDEX, if you take care to use a Unicode constant (but I'd spell out the NCHAR anyway, because invisible characters are confusing):

select patindex( N'%‭%'  ,@str COLLATE Latin1_General_BIN2)