4

The code of one of the hebrew characters which is a dot(=vowel) is 1463 or 0x05b7

I try to select only words that contain this character, but I get the whole list of words.

I try:

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

I tried also

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's not working

PS when I try the same with a letter code like 1488 it is working fine

eg. 

select * from words where word LIKE '%'+NCHAR(1488)+'%'
marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
Eli Cohen
  • 121
  • 9

1 Answers1

3

You can get the correct results if you COLLATE the source nvarchar as Latin1_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:

enter image description here

TT.
  • 14,883
  • 6
  • 41
  • 77
  • great! it works also when I use collate hebrew_bin, and without the N before '%' – Eli Cohen Feb 22 '16 at 11:49
  • @EliCohen Ah yes, any `*_BIN` collation will do. The `N`'s aren't strictly necessary if there aren't any unicode characters in it, but I always do that for nvarchars. – TT. Feb 22 '16 at 12:06