>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
>
