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