Skip to main content

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.

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:

stringlessthanequal('a','a')

Output: Returns true since both values are the same.

String literal reference example:

stringlessthanequal('a','b')

Output: Returns true since a evaluates to be less than b.

String literal reference example:

stringlessthanequal('abc','x')

Output: Returns true since the first letter of the first string is less than the first letter of the second string.

Column reference example:

stringlessthanequal(string1,string2)

Output: Returns the evaluation of string1 column values being greater than string2 column values.

Collation refers to the organizing of written content into a standardized order. String comparison functions utilize collation rules for Latin. A summary of the rules:

  • 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:

Anmerkung

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

stringlessthanequal(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

Whentrue, matching is case-insensitive. Default isfalse.

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.

Anmerkung

This argument is not required. By default, matches are case-sensitive.

Required?

Data Type

Example Value

No

String literal evaluating to a Boolean

'true'

Examples

Tipp

For additional examples, see Common Tasks.

Example - Simple string comparisons

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

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'

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.