loosing unicode specification in where clause

Question

loosing unicode specification in where clause

sklinga

SSC Enthusiast

Points: 104
More actions
July 1, 2008 at 1:38 pm

#381449

select * from table1 with (nolock)
where last_name like N'%伊能静携子逛街女星初恋事%'
and org like 463 and user like 476
will return the right rows with the proper unicode values
but
select * from table1 with (nolock)
where last_name like N'%伊能静携子逛街女星初恋事%'
and org = 463 and user = 476
will not return the right number of rows and values
Its loosing its unicode (N) for the like of last name.
Any hints?

Viewing 12 posts - 1 through 12 (of 12 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Are the other two columns numeric, or strings?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sklinga SSC Enthusiast Points: 104 More actions · Answer 2

sklinga

SSC Enthusiast

Points: 104

July 1, 2008 at 4:27 pm

#836947

They are int's and the last name is nvarchar

GSquared SSC Guru Points: 260824 More actions · Answer 3

I just tried this in my ProofOfConcept database, and it seemed to work as expected:

create table #T (

Org int,

[User] int,

Last_Name nvarchar(1000))

insert into #T

select 463, 476, N'????????????' union all

select 1, 1, N'????????????'

select * from #t with (nolock)

where last_name like N'%????????????%'

and org = 463 and = 476

Returned the right row. Have to admit, I have no faintest clue what the string means, if anything, so maybe I'm being too literal in all this, but the code worked as expected.

Is it possible you have some setting on that table that's not the default standard?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sklinga SSC Enthusiast Points: 104 More actions · Answer 4

Thank!

Can I ask what version and sp you are running and what collation? I ran your test as well and it worked as expected on 2005 sp4. The table field was originally a varchar and was altered to a nvarchar.

sklinga SSC Enthusiast Points: 104 More actions · Answer 5

sklinga

SSC Enthusiast

Points: 104

July 2, 2008 at 11:27 am

#837357

Sorry type-o 2005 sp2

GSquared SSC Guru Points: 260824 More actions · Answer 6

SQL_Latin1_General_CP1_CI_AS collation.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sklinga SSC Enthusiast Points: 104 More actions · Answer 7

heres the plan when it does not work, you can see that it converts the unicode value

select * from #t1 with (nolock) where last_name like N'%????????????%' and org = 463 and = 476

|--Table Scan(OBJECT:([tempdb].[dbo].[#t1]), WHERE:([tempdb].[dbo].[#t1].[Org]=[@0] AND [tempdb].[dbo].[#t1].[User]=[@1] AND [tempdb].[dbo].[#t1].[Last_Name] like N'%????????????%'))

GSquared SSC Guru Points: 260824 More actions · Answer 8

I think that's just a display issue in the plan. I get the same thing with both versions of your query.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sklinga SSC Enthusiast Points: 104 More actions · Answer 9

Maybe, but when I remove the org and user I get this:

select org,,last_name from #t1 with (nolock) where last_name like N'%????????????%'

|--Table Scan(OBJECT:([tempdb].[dbo].[#t1]), WHERE:([tempdb].[dbo].[#t1].[Last_Name] like N'%????????????%'))

, it looks to me like its being doing an implicit conversion, and if I only user like operator:

select org,,last_name from #t1 with (nolock)

where last_name like N'%????????????%'

and org like 463 and like 476

it works every time

GSquared SSC Guru Points: 260824 More actions · Answer 10

Odd. I don't get that in mine. The plan just gets rid of the other two columns, but is otherwise identical.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sklinga SSC Enthusiast Points: 104 More actions · Answer 11

It is odd, I suspect its a feature of operator precedence and datatype precedence. But I have no real proof yet other than it happens.

Thank You! For your help!