COMPARE

Syntax

COMPARE(<any>;<any>)

Description

Compares the arguments of one column with the arguments of another column. Both columns must contain the same data type. Depending on the data type the results of this function return different values.

  • Integer, Float, and Date
    If your columns contain integers, float values or dates, then COMPARE() returns:
    • 1 if the value in the first column is greater than the value in the second column or if the value in the second column is null. 
    • 0 if the value in the first column is equal to the value in the second column.
    • -1 if the value in the first column is less than the value in the second column or if the value in the first column is null.
  • Boolean
    If your columns contain Boolean values, then COMPARE() returns:
    • 1 if the first column is true and the second column is false or if the second column is null.
    • 0 if both the first column and the second column contain the same value.
    • -1 if the first column is false and the second column is true or if the first column is null.
  • String
    If your columns contain strings, the COMPARE() function pairs the first characters of the string argument values and compare the Unicode value of those characters.
    • If it finds the first paired characters mismatch, it returns an integer representing the difference in the Unicode value of the mismatched characters as described below.
    • If it finds a match, it moves on to the next character and repeats the process until exhausting the string length of the shorter of the two string values. At that point, if the strings are the same but one column has more characters, then it compares the string length.
    • Character (Unicode value) comparison
      • If the first character in the first column has a larger Unicode value than the character in the second column or if the second column is null, COMPARE() returns the difference of the Unicode values as a positive integer.
      • If the first character in the first column is the same as in the second column, COMPARE() moves on to the second character until it finds a mismatch. If it does find a mismatch it doesn't move on to the string length comparison step, and returns the difference of the mismatched character Unicode values.
        • If string character length is exhausted in either column value but not the other, and the character comparison has returned 0, the function begins the string length comparison.
        • If all the characters in the first column are the same as in the second column, COMPARE() returns 0.
      • If the character in the first column has a smaller Unicode value than the character in the second column or if the second column is null, COMPARE() returns the difference as a negative integer.
      • If one column has an empty string value, the function recognizes that character length is exhausted and immediately moves on to the string length comparison.
    • String length comparison (This is only compared after all character pairs have been evaluated.)
      If the characters in the string in the first column are the same as the characters in the string in the second column, string length is compared.
      • If the string length in the first column is greater than the string length in the second column or if the second column is null, COMPARE() returns the difference as a positive integer.
      • If the string length in the first column is equal to the string length in the second column, COMPARE() returns 0. This means that both strings are the same.
      • If the string length in the first column is less than the string length in the second column or if the first column is null, COMPARE() returns the difference as a negative integer.

Examples

Data field type

Column1

Column2

COMPARE returns

Comment

Integer

15

35

-1

Column1 is less than Column2

Integer

23

23

0

Column1 is identical to Column2
Integer23null1Column1 has a value, Column2 doesn't

Float

7.6

2.3

1

Column1 is greater than Column2

Float

8.6

15.3

-1

Column1 is less than Column2
Floatnull15.3-1Column1 doesn't have a value, Column2 does

Date

Aug 2, 2011 3:55:03 PM

Oct 15, 2010 4:34:34 AM

1

Column1 is earlier than Column2

Date

Mar 2, 2002 1:24:12 PM

Mar 2, 2002 1:24:12 PM

0

Column1 is identical to Column2

Date

1 Apr, 2010 10:56:03 AM

25 Apr, 2010 5:12:19 PM

-1

Column1 is later than Column2
Date1 Apr, 2010 10:56:03 AMnull1Column1 has a value, Column2 doesn't

Boolean

true

true

0

Column1 is identical to Column2

Boolean

false

true

-1

Column1 is false and Column2 is true

Boolean

true

false

1

Column1 is true and Column2 is false
Booleannulltrue-1Column1 doesn't have a value, Column2 is true
Booleannullfalse-1Column1 doesn't have a value, Column2 is false

String

Germany

George

3

"r" is 3 Unicode values higher than "o"

String

George

George

0

Column1 is identical to Column2

String

George

Germany

-3

"r" is 3 Unicode values lower than "o"

String

German

Germany

-1

Values start the same but Column1 has one fewer character than Column2

String

Germany

German

1

Values start the same but Column1 has one more character than Column2
StringGermanynull1Column1 has a value, Column2 doesn't
String<empty>George6Values start the same but Column1 has six more characters than Column2

nullnull0Column1 is identical to Column2