does anyone knows why '002' = '00²' in sqlserver 2000 ?


create table Test2 (veldnaam varchar(10),veldnaam2 varchar(10))
go
insert into test2 values ('002','00²')
go
select * from Test2 where veldnaam=veldnaam2


I did not expect to find a record !



Gene

Gene
I've got  nothing.
What is the version of SQL Server?



Show quoteHide quote

Show quote Hide quote Could you confirm the exact character codes in those fields

e.g.:
   select convert(varbinary(25),veldnaam),convert(varbinary(25),veldnaam2)
from Test2 where veldnaam=veldnaam2

Also, the collation settings
  select databasepropertyex(db_name(),'Collation')

The collation will affect comparators like '=', '>', etc.

Gene Vangampelaere wrote:
Show quoteHide quoteThat depends what collation you use. You didn't specify the collation
explicity so the default for your database will be used.

With this CREATE TABLE statement those values will be returned as
equal:
create table Test2 (veldnaam varchar(10) COLLATE Latin1_General_CI_AS,
veldnaam2 varchar(10) COLLATE Latin1_General_CI_AS)

With this one they won't:
create table Test2 (veldnaam varchar(10) COLLATE Latin1_General_BIN,
veldnaam2 varchar(10) COLLATE Latin1_General_BIN)

You can also specify the collation in your query:

SELECT *
FROM Test2
WHERE veldnaam=veldnaam2 COLLATE Latin1_General_BIN ;

thx !

It was a collation problem !



Gene Vangampelaere wrote:
Show quoteHide quoteThat depends what collation you use. You didn't specify the collation
explicity so the default for your database will be used.

With this CREATE TABLE statement those values will be returned as
equal:
create table Test2 (veldnaam varchar(10) COLLATE Latin1_General_CI_AS,
veldnaam2 varchar(10) COLLATE Latin1_General_CI_AS)

With this one they won't:
create table Test2 (veldnaam varchar(10) COLLATE Latin1_General_BIN,
veldnaam2 varchar(10) COLLATE Latin1_General_BIN)

You can also specify the collation in your query:

SELECT *
FROM Test2
WHERE veldnaam=veldnaam2 COLLATE Latin1_General_BIN ;

I am not seeing any record.

Whats your version/SP Level?


Show quoteHide quote

Well, they seem to have the same ASCII code in my collation:

SELECT ASCII('002'), ASCII('00²')
------------- --------------
48        48

The position of the digit on the line is a formatting one, I think... I
don't know that SQL Server will be able to tell them apart.


Show quoteHide quote

....in certain collations.  SQL Server does in fact store the varbinary
representation, so:

SELECT ASCII('002'), CONVERT(VARBINARY(12), '002')
SELECT ASCII('00²'), CONVERT(VARBINARY(12), '00²')

----------- --------------------------
48          0x303032
----------- --------------------------
48          0x3030FD

So you can treat this like a case sensitive query, and use explicit
collation hints or varbinary conversions in the query, or change your
collation to binary (this is a bit more drastic).  For some related
information see http://www.aspfaq.com/2152

SELECT ASCII('002'), ASCII('00²')  this will only look at the 0 you
will have to loop thru it
Check it out SELECT ASCII('002') , ASCII('00²') , ASCII('02'),
ASCII('0²') ,ASCII('0'), ASCII('0') all 48
How about this  SELECT ASCII('2'), ASCII('²')  these are not the same
(50 and 178)


http://sqlservercode.blogspot.com/

Yep, you're right, sorry.  See my follow-on...


SELECT ASCII('002'), ASCII('00²')  this will only look at the 0 you
will have to loop thru it
Check it out SELECT ASCII('002') , ASCII('00²') , ASCII('02'),
ASCII('0²') ,ASCII('0'), ASCII('0') all 48
How about this  SELECT ASCII('2'), ASCII('²')  these are not the same
(50 and 178)


http://sqlservercode.blogspot.com/

Evaluation of conditions
complex and large triggers
Apostrophes and Dynamic SQL
Table design question on LARGE table
Query help