List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:November 8 2010 8:11pm
Subject:Re: numeric comparisons
View as plain text  
>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.

Cast() may happen to fix some rounding errors, but the only way to be 
sure of getting rid of such rounding errors in float or double values is 
to change the /storage type/ to decimal.

PB

----

On 11/8/2010 12:50 PM, Larry Martell wrote:
> 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