T-SQL: select words with specific Hebrew dots using the dot code

Question

The code for one of the Hebrew characters, which is a period (= vowel), is 1463 or 0x05b7

I try to select only words containing this character, but I get the entire list of words.

I'm trying to:

DECLARE @d NCHAR
set @d = NCHAR(1463)
select * from words where word like '%' + @d + '%'

I also tried

select * from words where word LIKE '%'+NCHAR(0x05B7)+'%'

I tried to finish the statement with

collate hebrew_cs_as

or

collate hebrew_cs_ai

and it does not work

PS, when I try to do the same with the letter code, for example 1488, it works fine

eg.

select * from words where word LIKE '%'+NCHAR(1488)+'%'

Answer 1

You can get the correct results if the COLLATEoriginal nvarchar is likeLatin1_General_BIN

DECLARE @t TABLE(txt NVARCHAR(4000));
INSERT INTO @t(txt)VALUES
    (NCHAR(1463)),(N'abcdef'),(N'aiiy'+NCHAR(1463)+N'skj'),(N'sdljsd'),(N'sdjp'+NCHAR(1463)),(N'sdzf');
SELECT * FROM @t WHERE txt COLLATE Latin1_General_BIN LIKE N'%'+NCHAR(1463)+N'%';

Result: