ColdFusion large number comparison

Question

ColdFusion thinks 10090000000557765 = 10090000000557763.

I get why but I need to know the best way for ColdFusion to know they are NOT the same. I read about the Compare() method but that also returns true. I've casted them as strings and also returns true.

I thought about writing a custom function to break the string into two parts, compare each individually and then return true/false but that seems dumb.

I've Tried the following:

Val(a) EQ Val(b)
ToString(a) EQ ToString(b)
a.compareTo(b)

For clarification. I'm using a DB I don't control that uses bigint. I learned early on that Javascript couldn't handle these so I converted all the bigint fields to varchar in my models. Now however, CF is having issue comparing the strings and I can't it seems convert them back to numbers.

Example I just did:

<cfif '10090000000557765' EQ '10090000000557763'>
True
<cfelse>
False
</cfif>

On http://cflive.net/ and it returned true. See my note. In the DB they are bigint. I had to cast them as VarChar when getting them out because Javascript can't handle bigint but it does strings just fine.

Answer 1

Short Answer: So as not to overlook the obvious, java methods like Long.compareTo() return -1, 0, or 1. Make sure you are using the correct expression to test for equality. The result will be 0 when the values are equal. (Zero (0) evaluates to false in CF).

Longer answer: How the values are stored, retrieved and specifically evaluated is very relevant here. All of the methods you mentioned worked fine for me under CF11 (with the exception of val, for obvious reasons). Which suggests something is different in your code or environment.

For example, using CF11 and SQL Server:

CREATE TABLE test
(
    bigIntValue1 BIGINT
    , bigIntValue2 BIGINT
    , varcharValue1 VARCHAR(50)
    , varcharValue2 VARCHAR(50)
)

The following test code:

writeOutput("<br>qry.bigIntValue1: "&  val(qry.bigIntValue1) );
writeOutput("<br>qry.bigIntValue2: "&  val(qry.bigIntValue2) );
writeOutput("<br>BigInt Val(): "& ( val(qry.bigIntValue1) eq val(qry.bigIntValue2)) );
writeOutput("<br>Varchar Val(): "& ( val(qry.varcharValue1) eq val(qry.varcharValue2)) );
writeOutput("<br>Long.compareTo: "& qry.bigIntValue1[1].compareTo(qry.bigIntValue2[1]));
writeOutput("<br>Varchar.compareTo: "& qry.varcharValue1[1].compareTo(qry.varcharValue2[1]));
writeOutput("<br>Compare(Long, Long) "& compare(qry.bigIntValue1, qry.bigIntValue2));
writeOutput("<br>Compare(Varchar, Varchar) "& compare(qry.varcharValue1, qry.varcharValue2));

Produces these results:

val(qry.bigIntValue1):     1.00900000006E+016
val(qry.bigIntValue21):    1.00900000006E+016
BigInt Val():              YES
Varchar Val():             YES

Long.compareTo:            1
Varchar.compareTo:         2
Compare(Long, Long):       1
Compare(Varchar, Varchar): 1 

As expected, the val() comparison returns the wrong answer, because the function implicitly converts the large numbers to an approximate type. Once converted, these approximate values are considered equal.

All of the other comparisons indicate the values are NOT equal. While the examples use different functions, they all have one thing in common: they all return 0 when the values are considered equal. Anything else indicates the values are different. Since zero (0) is treated as false in CF, be sure you are using the correct expression to test for equality and differences.

• Long.compareTo() - (Data type used to represent BigInt in CF) the value 0 if this Long is equal to the argument Long; a value less than 0 if this Long is numerically less than the argument Long; and a value greater than 0 if this Long is numerically greater than the argument Long (signed comparison)."

• String.compareTo() - the value 0 if the argument string is equal to this string; a value less than 0 if this string is lexicographically less than the string argument; and a value greater than 0 if this string is lexicographically greater than the string argument.

• Compare(string1, string2) - "...Performs a case sensitive comparison of two strings.? Returns -1 (less than), 0 (equal) or 1 (greater than)