SQLTeam.com | Weblogs | Forums

Select words with specific character using NCHAR(CODE)

sql2008r2

#1

the code of one of the hebrew characters which is a dot(=vouwel) 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 workin fine eg.

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


#2

I got an answer to this question here
StackOverflow

SELECT * FROM @t WHERE txt COLLATE Latin1_General_BIN LIKE N'%'+NCHAR(1463)+N'%';

but I use

SELECT * FROM @t WHERE txt COLLATE HEBREW_BIN LIKE '%'+NCHAR(1463)+'%';

The two options are good!


#3

I don't know why specific, different, BINARY collations exist - they must all be the same, surely? Perhaps there is an association with a Code Page that matters ...

But unless someone says differently AFAIAC all Binary collations are the same in this regard.

I suspect that, absent a Binary Collation, the dot(=vouwel) in Hebrew is being matched with any other "dot" (or some other, common, character) and Case Sensitive doesn't alter that (and, as yo have discovered, Binary is the comparison that you need for an "exact" match)