Comparison Functions

In Spectrum equality and inequality functions are called comparison functions. These functions allow you to compare a column's value with another column's value within your Workbook. They let you know if a value is greater than, less than, equal to, and more.

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.

Results by Data Type

'Integer', 'Float', and 'Date'

If your columns contain integers, float values or dates, then COMPARE() returns:

1. '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.
2. '0' if the value in the first column is equal to the value in the second column.
3. '-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. '1' if the first column is true and the second column is false or if the second column is null.
2. '0' if both the first column and the second column contain the same value.
3. '-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 will pair the first characters of the string argument values and compare the Unicode value of those characters.

1. If it finds the first paired characters mismatch, it will return an integer representing the difference in the Unicode value of the mismatched characters as described below.
2. If it finds a match, it will move 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.
3. Character (Unicode value) comparison
4. 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() will return the difference of the Unicode values as a positive integer.
5. If the first character in the first column is the same as in the second column, COMPARE() will move 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.
   1. If string character length is exhausted in either column value but not the other, and the character comparison has returned '0', the function will begin the string length comparison.
   2. If all the characters in the first column are the same as in the second column, COMPARE() will return '0'.
6. 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() will return the difference as a negative integer.
7. If one column has an empty string value, the function will recognize that character length is exhausted and immediately moves on to the string length comparison.
8. 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 will be compared.
9. 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() will return the difference as a positive integer.

10. If the string length in the first column is equal to the string length in the second column, COMPARE() will return '0'.

    This means that both strings are the same.

11. 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() will return 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
Integer	23	null	1	Column1 has a value, Column2 does not
Float	7.6	2.3	1	Column1 is greater than Column2
Float	8.6	15.3	-1	Column1 is less than Column2
Float	null	15.3	-1	Column1 does not 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
Date	1 Apr, 2010 10:56:03 AM	null	1	Column1 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
Boolean	null	true	-1	Column1 doesn't have a value, Column2 is true
Boolean	null	false	-1	Column1 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
String	Germany	null	1	Column1 has a value, Column2 doesn't
String	<empty>	George	6	Values start the same but Column1 has six more characters than Column2

EQUALS

Syntax

EQUALS(<any>;<any>)

Description

Checks to see if two values are identical by comparing the arguments of one column with the arguments of another column. Both columns must contain the same data type. EQUALS then returns a Boolean value, either true if both arguments are equal or false if the arguments are different. If one of the arguments is null, EQUALS returns false.

Instead of using a function, you could also use the equality operator ==.

Examples

Data field type	Column1	Column2	EQUALS returns
Integer	15	35	false
Integer	23	23	true
Integer	23	null	false
Float	7.6	7.6	true
Float	8.6	15.3	false
Float	15.3	null	false
Date	Aug 2, 2011	Oct 15, 2010	false
Date	Mar 2, 2002	Mar 2, 2002	true
Date	null	Mar 2, 2002	false
Boolean	false	true	false
Boolean	true	true	true
Boolean	true	null	false
String	Germany	George	false
String	George	George	true
String	German	Germany	false
String	null	George	false
List	[ 1, 2, 3, 4	[ 1, 2, 3, 4	true
List	[ 1, 2, 3, 4	[ 5, 6, 7,	false
/	null	null	true

GE

Syntax

GE(<any>;<any>)

Description

Checks to see if the arguments in Column1 are greater than or equal to the arguments in Column2. Both columns must contain the same data type. GE then returns a boolean value, either true if the arguments in Column1 are greater than or equal to the arguments in Column2 or false if the arguments in Column1 are not greater than or equal to the arguments in Column2. If one of the arguments is null, that value is lesser than other values, unless all arguments are null.

When comparing strings, first the characters are paired, then the Unicode value of these pairs are compared. If the character pairs are the same, the length of the string is compared. See COMPARE for more information, as these functions operate similarly.

Instead of using a function, you could also use the equality operator >=

Examples

Data field type	Column1	Column2	GE returns
Integer	15	35	false
Integer	23	23	true
Integer	15	null	true
Float	7.6	7.6	true
Float	10.7	8.6	true
Float	null	7.6	false
Date	Aug 2, 2011	Oct 15, 2010	true
Date	Mar 2, 2002	Mar 2, 2002	true
Date	Mar 2, 2002	Apr 2, 2010	false
Date	Mar 2, 2002	null	true
Boolean	false	true	false
Boolean	true	false	true
Boolean	false	false	true
Boolean	null	true	false
String	Germany	George	true
String	George	George	true
String	German	Germany	false
String	George	null	true
\	null	null	true

GT

Syntax

GT(<any>;<any>)

Description

Checks to see if the arguments in Column1 are greater than the arguments in Column2. Both columns must contain the same data type. GT then will return a Boolean value, either true if the arguments in Column1 are greater than the arguments in Column2 or false if the arguments in Column1 are not greater than the arguments in Column2. If one of the arguments is null, that value will be lesser than other values, unless all arguments are null. When comparing strings, first the characters are paired, then the Unicode value of these pairs are compared. If the character pairs are the same, the length of the string will be compared.

Instead of using a function, you could also use the equality operator >

Examples

Data Field Type	Column1	Column2	GT returns
Integer	15	35	false
Integer	23	23	false
Integer	15	null	true
Float	7.6	7.6	true
Float	10.7	8.6	false
Float	null	7.6	false
Date	Aug 2, 2011 3:55:03 PM	Oct 15, 2010 4:34:34 AM	true
Date	Mar 2, 2002 1:24:12 PM	Mar 2, 2002 1:24:12 PM	false
Date	Mar 2, 2002 1:24:12 PM	Apr 2, 2010 11:42:21 PM	false
Date	Mar 2, 2002 1:24:12 PM	null	true
Boolean	false	true	false
Boolean	true	false	true
Boolean	true	true	false
Boolean	null	true	true
String	Germany	George	true
String	George	George	false
String	German	Germany	false
String	George	null	false
/	null	null	false

LE

Syntax

LE(<any>;<any>)

Description

Checks to see if the arguments in Column1 are less than or equal to the arguments in Column2. Both columns must contain the same data type, either true if the arguments in Column1 are less than or equal to the arguments in Column2 or false if the arguments in Column1 are not less than or equal to the arguments in Column2. If one of the arguments is null, that value will be lesser than other values, unless all arguments are null. When comparing strings, first the characters are paired, then the Unicode value of these pairs are compared. If the character pairs are the same, the length of the string will be compared.

Instead of using a function, you could also use the equality operator <=

Examples

Data Field Type	Column1	Column2	LE returns
Integer	15	35	true
Integer	23	23	true
Integer	null	15	true
Float	7.6	7.6	true
Float	10.7	8.6	false
Float	10.7	null	false
Date	Aug 2, 2011 3:55:03 PM	Oct 15, 2010 4:34:34 AM	false
Date	Mar 2, 2002 1:24:12 PM	Mar 2, 2002 1:24:12 PM	true
Date	Mar 2, 2002 1:24:12 PM	Apr 2, 2010 11:42:21 PM	true
Date	Mar 2, 2002 1:24:12 PM	null	true
Boolean	false	true	true
Boolean	true	false	false
Boolean	false	false	true
Boolean	false	null	false
String	Germany	George	false
String	George	George	true
String	German	Germany	true
String	null	George	true
/	null	null	true

LT

Syntax

LT(<any>;<any>)

Description

Checks to see if the arguments in Column1 are less than the arguments in Column2. Both columns must contain the same data type. LT then returns a Boolean value, either true if the arguments in Column1 are less than the arguments in Column2 or false if the arguments in Column1 are not less than the arguments in Column2. If one of the arguments is null, that value will be lesser than other values, unless all arguments are null. When comparing strings, first the characters are paired, then the Unicode value of these pairs are compared. If the character pairs are the same, the length of the string will be compared.

Instead of using a function, you could also use the equality operator <

Examples

Data Field Type	Column1	Column2	LT returns
Integer	15	35	true
Integer	23	23	false
Integer	15	null	false
Float	7.6	15.3	true
Float	10.7	8.6	false
Float	10.7	null	true
Date	Aug 2, 2011 3:55:03 PM	Oct 15, 2010 4:34:34 AM	false
Date	Mar 2, 2002 1:24:12 PM	Mar 2, 2002 1:24:12 PM	false
Date	Mar 2, 2002 1:24:12 PM	Apr 2, 2010 11:42:21 PM	true
Date	Mar 2, 2002 1:24:12 PM	null	false
Boolean	false	true	true
Boolean	true	false	false
Boolean	false	false	false
Boolean	false	null	true
String	Germany	George	false
String	George	George	false
String	German	Germany	true
String	null	George	false
	null	null	false

NOT_EQUALS

Syntax

NOT_EQUALS(<any>;<any>)

Description

Checks to see if two values are different by comparing the arguments of one column with the arguments of another column. Both columns must contain the same data type. NOT_EQUALS then returns a Boolean value, either true if both arguments are different or false if the arguments are equal. If one of the arguments is null, NOT_EQUALS returns TRUE.

Examples

Data Field Type	Column1	Column2	NOT_EQUALS returns
Integer	15	35	true
Integer	23	23	false
Integer	15	null	true
Float	7.6	7.6	false
Float	10.7	8.6	true
Float	null	7.6	true
Date	Aug 2, 2011 3:55:03 PM	Oct 15, 2010 4:34:34 AM	true
Date	Mar 2, 2002 1:24:12 PM	Mar 2, 2002 1:24:12 PM	false
Date	Mar 2, 2002 1:24:12 PM	null	true
Boolean	false	true	true
Boolean	true	true	false
Boolean	null	true	true
String	Germany	George	true
String	George	George	false
String	George	null	false
	null	null	false