EXAMPLE - String Comparison Functions

The following example demonstrates functions that can be used to compare two sets of strings. These functions include the following:

Source:

The following table contains some example strings to be compared.

rowIdstringAstringB
1aa
2aA
3ab
4a1
5a;
6;1
7a a
8aaa
9abcx

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

Transform:

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

derive value: STRINGGREATERTHAN(stringA,stringB) as: 'greaterThan'

derive value: STRINGGREATERTHANEQUAL(stringA,stringB) as: 'greaterThanEqual'

derive value: STRINGLESSTHAN(stringA,stringB) as: 'lessThan'

derive value: STRINGLESSTHANEQUAL(stringA,stringB) as: 'lessThanEqual'

Results:

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

rowIdstringAstringBlessThanEquallessThangreaterThanEqualgreaterThanNotes
1aatruefalsetruefalseEvaluation differences between STRINGLESSTHAN and STRINGGREATERTHAN and greater than versions.
2aAtruetruefalsefalseComparisons are case-sensitive. Uppercase letters are greater than lowercase letters.
3abtruetruefalsefalseLetters later in the alphabet (b) are greater than earlier letters (a).
4a1falsefalse
true true Letters (a) are greater than digits (1).
5a;falsefalsetruetrueLetters (a) are greater than non-alphanumerics (;).
6;1truetruefalsefalse

Digits (1) are greater than non-alphanumerics (;). Therefore, the following characters are listed in order of evaluation:

Aa1;
7a afalsefalsetruetrueLetters (and any non-breaking character) are greater than space values.
8aaatruetruefalsefalseThe second string is greater, since it contains one additional string at the end.
9abcxtruetruefalsefalseThe second string is greater, since its first letter is greater than the first letter of the first string.
Was this page helpful? Let us know how we did:

Send feedback about...

Google Cloud Dataprep Documentation