wrong select result using like on nvarchar column

Question

Post reply

wrong select result using like on nvarchar column

fedebona

Old Hand

Points: 302
More actions
November 17, 2010 at 9:10 am

#229337

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

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.

shump66@yahoo.com Right there with Babe Points: 791 More actions · Answer 2

fedebona (11/17/2010)
Please look at the following code:

I took your code ran it on my server and it work as expected

I got one row back?

Scott

Scott
I am a Senior Data Analyst/DBA
I work with MS SQL, Oracle & several ETL Tools

@thewolf66[/url]

Lowell SSC Guru Points: 323504 More actions · Answer 3

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

fedebona Old Hand Points: 302 More actions · Answer 4

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?