List:General Discussion« Previous MessageNext Message »
From:Larry Martell Date:November 8 2010 6:50pm
Subject:numeric comparisons
View as plain text  
I have a client that asked me to look into a situation where they were
seeing different behavior with the same data and same sql on 2
different servers.

The have some sql that was comparing a double to a varchar in a where
clause - something like this:

where (doubleCol > varcharCol and some other conditions) or
           (doubleCol < varcharCol and some other conditions)

Let's take an example where the varcharCol has a string in like
'4.5000' and the doubleCol has 4.5.

On their 5.0.45 server this was working as 'expected' (i.e. the
comparisons in the where clause were false since they are numerically
equal). But on their 5.0.18 server the > clause was resolving to true.
 I changed the query to cast both columns to binary, so the query now
reads:

where (cast(doubleCol as binary) > cast(varcharCol as binary) and some
other conditions) or
           (cast(doubleCol as binary) < cast(varcharCol as binary) and
some other conditions)

And now on both servers the < clause of the query is resolving to true.

I know I can strip off the trailing zeros from the varchar, but there
must be a generic way to cast these so they compare properly as
numbers.

TIA
-larry
Thread
numeric comparisonsLarry Martell8 Nov
  • Re: numeric comparisonsMichael Satterwhite8 Nov
    • Re: numeric comparisonsLarry Martell8 Nov
      • RE: numeric comparisonsJerry Schwartz8 Nov
  • Re: numeric comparisonsPeter Brawley8 Nov