Contents:
true
if the first string evaluates to be greater than the second string, based on a set of common collation rules. Source values can be string literals, column references, or expressions that evaluate to strings.
Wrangle vs. SQL: This function is part of Wrangle, a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.
Basic Usage
String literal reference example:
stringgreaterthan('a','b')
Output: Returns false
since a
evaluates to be less than b
.
String literal reference example:
stringgreaterthan('a','ab')
Output: Returns false
since the second string contains an additional letter.
String literal reference example:
stringgreaterthan('abc','x')
Output: Returns false
since the first letter of the first string is less than the first letter of the second string.
Column reference example:
stringgreaterthan(string1,string2)
Output: Returns the evaluation of string1
column values being greater than string2
column values.
- Comparisons are case-sensitive.
- Uppercase letters are greater than lowercase versions of the same letter.
- However, lowercase letters that are later in the alphabet are greater than the uppercase version of the previous letter.
- Two strings are equal if they match identically.
- If two strings are identical except that the second string contains one additional character at the end, the second string is greater.
- A normalized version of a letter is the unaccented, lowercase version of the letter. In string comparison, it is the lowest value of all of its variants.
- a is less than ă.
- However, when compared to b, a = ă.
- The set of Latin normalized characters contains more than 26 characters.
This table illustrates some generalized rules of Latin collation.
Order | Description | Lesser Example | Greater Example |
---|---|---|---|
1 | whitespace | (space) | (return) |
2 | Punctuation | ' | @ |
3 | Digits | 1 | 2 |
4 | Letters | a | A |
5 | A | b |
Resources:
NOTE: In the following set of charts (linked below), the values at the top of the page are lower than the values listed lower on the page. Similarly, the charts listed in the left nav bar are listed in ascending order.
For more information on the applicable collation rules, see http://www.unicode.org/charts/collation/.
Syntax and Arguments
stringgreaterthan(string_ref1,string_ref2 <span>[,ignore_case]</span>)
Argument | Required? | Data Type | Description |
---|---|---|---|
string_ref1 | Y | string | Name of first column or first string literal to apply to the function |
string_ref2 | Y | string | Name of second column or second string literal to apply to the function |
ignore_case | N | string | When true , matching is case-insensitive. Default is false . |
For more information on syntax standards, see Language Documentation Syntax Notes.
string_ref1, string_ref2
String literal, column reference, or expression whose elements you want to compare based on this function.
Usage Notes:
Required? | Data Type | Example Value |
---|---|---|
Yes | String literal, column reference, or expression evaluating to a string | myString1, myString2 |
ignore_case
When true
, matches are case-insensitive. Default is false
.
NOTE: This argument is not required. By default, matches are case-sensitive.
Required? | Data Type | Example Value |
---|---|---|
No | String literal evaluating to a Boolean | 'true'
|
Tip: For additional examples, see Common Tasks.
Examples
Example - Simple string comparisons
Source: The following table contains some example strings to be compared. Note that in row #6, Transformation: For each set of strings, the following functions are applied to generate a new column containing the results of the comparison. Results: In the following table, the Digits (1) are greater than non-alphanumerics (;). Therefore, the following characters are listed in order of evaluation:STRINGGREATERTHAN
- Evaluates to true
if the first string is greater than the second string. See STRINGGREATERTHAN Function. STRINGGREATERTHANEQUAL
- Evaluates to true
if the first string is greater than or equal to the second string. See STRINGGREATERTHANEQUAL Function.STRINGLESSTHAN
- Evaluates to true
if the first string is less than the second string. See STRINGLESSTHAN Function.STRINGLESSTHANEQUAL
- Evaluates to true
if the first string is less than or equal to the second string. See STRINGLESSTHANEQUAL Function.EXACT
- Evaluates to true
if the first string is an exact match with the second string. See EXACT Function.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
stringB
begins with a space character.
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'
Transformation Name
New formula
Parameter: Formula type
Single row formula
Parameter: Formula
EXACT(stringA,stringB)
Parameter: New column name
'exactEqual'
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
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.
This page has no comments.