Special character search
-
I am trying to do a search for special characters using like
When I run my query
select * from tblXMLOrderLotShipping
where shipto like '%Œ%'
to search for Œ
None of the results returned contain Œ.
Can anyone explain why this is haappening and how to get around it?
-
Couple of questions:
What is the data type of "shipto"?
What is the Collation of your database ?
* Noel -
I used the ASCII function to get the ascii code for Œ, which is 140 so write a function like so;
select * from tblXMLOrderLotShipping
where charindex(char(140),shipto) > 0
hope that helps.
-
No the Charindex returns the same result set. The one thing that the results have in common in the characters O and E. Strange because the look the same. The Colation on this database is SQL_Latin1_General_CP1_CI_AS.
What in the heck is Πany way?
-
Oh and the data type is nvarchar.
-
"Shipto" DATA TYPE, Please
* Noel -
The shipto datatype is nvarchar
-
The shipto datatype is nvarchar
-
maybe this will help you
DECLARE @T TABLE (shipto nvarchar(50))
insert into @T select N'aaaaa'+nchar(140)+N'bbbbbbbb'
insert into @T select N'bbbbbb'+nchar(140)+N'bbbbbbbb'
insert into @T select N'aaaaa'+N'aaaaaaa'
select * from @T
where shipto like N'%'+nchar(140)+N'%'
select * from @T
where shipto like N'%'+N'Œ'+N'%'
print unicode(N'Œ')
print ascii('Œ')
Kindest Regards,Vasc
-
Sorry I coudn't get back earlier.
To make the long story short it is the conversion to Unicode what messes up the data because 'Œ' is not part of the unicode standard
and because the comparison with LIKE , IN CHARINDEX, etc will try to "implicitly" convert to a unicode expression (data type nvarchar) you get the unexpected results.
a workaround maybe
select * from tblXMLOrderLotShipping
where cast(shipto as varchar(50)) like '%Œ%
I know is ugly but I could not prevent unicode converssion
You could also perform binary comparison to get around that issue hth
* Noel
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply