EXAMPLE - String Comparison Functions

This example demonstrates functions that can be used to compare two sets of strings.

Functions:

Item	Description
STRINGGREATERTHAN Function	Returns `true` if the first string evaluates to be greater than the second string, based on a set of common collation rules.
STRINGGREATERTHANEQUAL Function	Returns `true` if the first string evaluates to be greater than or equal to the second string, based on a set of common collation rules.
STRINGLESSTHAN Function	Returns `true` if the first string evaluates to be less than the second string, based on a set of common collation rules.
STRINGLESSTHANEQUAL Function	Returns `true` if the first string evaluates to be less than or equal to the second string, based on a set of common collation rules.
EXACT Function	Returns `true` if the second string evaluates to be an exact match of the first string. Source values can be string literals, column references, or expressions that evaluate to strings.

Source:

The following table contains some example strings to be compared.

Note that in row #6, stringB begins with a space character.

Transformation:

For each set of strings, the following functions are applied to generate a new column containing the results of the comparison.

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	STRINGGREATERTHAN(stringA,stringB)
Parameter: New column name	'greaterThan'

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	STRINGGREATERTHANEQUAL(stringA,stringB)
Parameter: New column name	'greaterThanEqual'

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	STRINGLESSTHAN(stringA,stringB)
Parameter: New column name	'lessThan'

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	STRINGLESSTHANEQUAL(stringA,stringB)
Parameter: New column name	'lessThanEqual'

Results:

In the following table, the Notes column has been added manually.


true
false
false
true
false
Aa1;
false
false
false

rowId	stringA	stringB	lessThanEqual	lessThan	greaterThanEqual	greaterThan	exactEqual	Notes
1	a	a	true	false	true	false	true	Evaluation of differences between `STRINGLESSTHAN` and `STRINGGREATERTHAN` and greater than versions.
2	a	A	true	true	false	false	false	Comparisons are case-sensitive. Uppercase letters are greater than lowercase letters.
3	a	b	true	true	false	false	`false`	Letters later in the alphabet (b) are greater than earlier letters (a).
4	a	1	false	false	true	true	`false`	Letters (a) are greater than digits (1).
5	a	;	false	false	true	true	false	Letters (a) are greater than non-alphanumerics (;).
6	;	1	true	true	false	false	false	Digits (1) are greater than non-alphanumerics (;). Therefore, the following characters are listed in order of evaluation: Aa1;
7	a	a	false	false	true	true	false	Letters (and any non-breaking character) are greater than space values.
8	a	aa	true	true	false	false	false	The second string is greater, since it contains one additional string at the end.
9	abc	x	true	true	false	false	false	The second string is greater, since its first letter is greater than the first letter of the first string.

In this section:

Search results