EXAMPLE - String Comparison Functions

Contents:

Expand all Collapse all

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.

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.

See Also for EXAMPLE - String Comparison Functions: