binary compare - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

binary compare

Author

Topic

stultuske
Starting Member

3 Posts

Posted - 2006-04-25 : 06:04:26

I'm transferring a database from MS Access to MS SQL Server, but got a little stuck on the next problem.

I'm trying to implement the next function (used in the original MS Access DB)

StrComp([tblOrdersDetails.PartNumber],[tblOrdersOutDetails.PartNumber],0) AS vergelijk

into the new SQL Server DB

I've translated it to:

CASE WHEN dbo.tblOrdersDetails.partnumber LIKE dbo.tblOrdersOutDetails.partnumber THEN 0 ELSE CASE WHEN dbo.tblOrdersDetails.partnumber < dbo.tblOrdersOutDetails.partnumber
THEN - 1 ELSE 1 END END AS Vergelijk

which gives (almost) the same result.
in two occasions, where the strings to be compared are (for instance)
CX500 Cursus Adv Admin
the comparison in MS Access returns -1, while SQL Server returns 1.

My guess is, it's because the compare in MS Access is done binary, but how do I do this in SQL Server?

It wouldn't be called research if we knew what we're doing, now would it?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-25 : 06:27:43

I'm not sure about 'Adv/Admin', but it looks like the collate statement could come in handy for you. Take a look at the following...

--data
declare @t table (x varchar(10))
insert @t
          select 'CX500'
union all select 'Cursus'
union all select 'Adv'
union all select 'Admin'

--calculation
select * from @t order by x
select * from @t order by x collate Latin1_General_BIN

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

stultuske
Starting Member

3 Posts

Posted - 2006-05-17 : 06:58:14

maybe my post was a little unclear :) I used tabs to seperate the Strings, but the forum didn't take them..

anyway, here what I ment, and the solution:

I needed to (binary) compare the next two values
CX500
Cursus Adv Admin

which gave the wrong result (since it wasn't compared binary)

the next statement gave me the right solution:

case when cast(dbo.tblOrdersDetails.partnumber as varbinary) like cast(dbo.tblOrdersOutDetails.partnumber as varbinary) then 0 else
case when cast(dbo.tblOrdersDetails.partnumber as varbinary) < cast(dbo.tblOrdersOutDetails.partnumber as varbinary)
then - 1 else 1 end end as Vergelijk

It wouldn't be called research if we knew what we're doing, now would it?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-05-17 : 07:39:49

quote:
Originally posted by stultuske
the next statement gave me the right solution:

case when cast(dbo.tblOrdersDetails.partnumber as varbinary) like cast(dbo.tblOrdersOutDetails.partnumber as varbinary) then 0 else
case when cast(dbo.tblOrdersDetails.partnumber as varbinary) < cast(dbo.tblOrdersOutDetails.partnumber as varbinary)
then - 1 else 1 end end as Vergelijk

Why are you using LIKE when the righthand side is not a pattern?
Casting to varbinary without a length will truncate at 30 bytes.
StrComp("Adv","Admin",0) returns 1 for me!

I agree with Ryan, use a COLLATE.

CASE WHEN dbo.tblOrdersDetails.partnumber COLLATE Latin1_General_BIN = dbo.tblOrdersOutDetails.partnumber COLLATE Latin1_General_BIN THEN 0
WHEN dbo.tblOrdersDetails.partnumber COLLATE Latin1_General_BIN < dbo.tblOrdersOutDetails.partnumber COLLATE Latin1_General_BIN
THEN - 1 ELSE 1 END AS Vergelijk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources