November 17, 2010 at 9:10 am
Please look at the following code:
declare @tab table(foo nvarchar(40))
insert into @tab values (N'aaaa')
insert into @tab values (N'bbbb')
insert into @tab values (N'cccc')
insert into @tab values (N'unicodeརtibetan')
select * from @tab where foo like N'%ར%' --Does retrieve all!!!!!
It should give only a row but it returns all records.
Seems like the combination of wildcard % and unicode char gives an issue.
If I put N'%eར%' or N'%རt%' I get the right result of 1 row (Tested with 2005 and 2008)
What I'm doing wrong?
Thanks
Federico
November 17, 2010 at 9:44 am
I wonder if that weird character is like _ or % in Tibetian? :w00t::w00t:
Try something like this: where foo like '%[?]%'
The probability of survival is inversely proportional to the angle of arrival.
November 18, 2010 at 4:21 am
November 18, 2010 at 4:32 am
i believe it has to do with collations.
on my SQL 2008, i get all rows back unless i compare with a binary collation:
declare @tab table(foo nvarchar(40) COLLATE SQL_Latin1_General_CP1_CI_AS)
insert into @tab values (N'aaaa')
insert into @tab values (N'bbbb')
insert into @tab values (N'cccc')
insert into @tab values (N'unicode?tibetan')
select * from @tab where foo like N'%?%' --Does retrieve all!!!!!
GO
declare @tab table(foo nvarchar(40) COLLATE Latin1_General_BIN)
insert into @tab values (N'aaaa')
insert into @tab values (N'bbbb')
insert into @tab values (N'cccc')
insert into @tab values (N'unicode?tibetan')
select * from @tab where foo like N'%?%' --one row!!!!!
select * from ::fn_helpcollations()
Lowell
November 18, 2010 at 4:36 am
shump66 (11/18/2010)
I took your code ran it on my server and it work as expected
I got one row back?
Scott
I get back the entire table, instead. Making more tests I verified that the issue depends on which Unicode Script the char belongs. If I use a unicode char from Arabian, this doesn't happens; results are correctly filtered and I get only a record.
So what can influence different results? Can it be related to collation?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply