# 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.

rowId

stringA

stringB

1
a
a
2
a
A
3
a
b
4
a
1
5
a
;
6
;
1
7
a
 a
8
a
aa
9
abc
x

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 Single row formula STRINGGREATERTHAN(stringA,stringB) 'greaterThan'
 Transformation Name New formula Single row formula STRINGGREATERTHANEQUAL(stringA,stringB) 'greaterThanEqual'
 Transformation Name New formula Single row formula STRINGLESSTHAN(stringA,stringB) 'lessThan'
 Transformation Name New formula Single row formula STRINGLESSTHANEQUAL(stringA,stringB) 'lessThanEqual'
 Transformation Name New formula Single row formula EXACT(stringA,stringB) 'exactEqual'

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.