ESQL comparison operators

ESQL provides the following comparison operators (predicates):

Operator >
The result is TRUE if the value of the first operand is greater than that of the second. Otherwise the result is FALSE (but see the note on NULL values following this list).
Operator <
The result is TRUE if the value of the first operand is less than that of the second. Otherwise the result is FALSE (but see the note on NULL values following this list).
Operator >=
The result is TRUE if the value of the first operand is greater than or is equal to that of the second. Otherwise the result is FALSE (but see the note on NULL values following this list).
Operator <=
The result is TRUE if the value of the first operand is less than or is equal to that of the second. Otherwise the result is FALSE (but see the note on NULL values following this list).
Operator =
The result is TRUE if the value of the first operand is equal to that of the second. Otherwise the result is FALSE (but see the note on NULL values following this list). This operator can compare scalars, rows, and lists. See ROW and LIST comparisons for a description of list and row comparison.
Operator <>
The result is TRUE if the value of the first operand is not equal to that of the second. Otherwise the result is FALSE (but see the note on NULL values following this list). This operator can compare scalars, rows, and lists. See ROW and LIST comparisons for a description of list and row comparison.
Operator BETWEEN
The operator BETWEEN allows you to test whether a value lies between two boundary values.

This operator exists in two forms, SYMMETRIC and ASYMMETRIC (the default if neither is specified). The SYMMETRIC form is equivalent to:

(source >= boundary1 AND source <= boundary2) OR
(source >= boundary2 AND source <= boundary1)

The ASYMMETRIC form is equivalent to:

source >= boundary1 AND source <= boundary2

The ASYMMETRIC form is simpler but returns only the result that you expect when the first boundary value has a smaller value than the second boundary. It is only useful when the boundary condition expressions are literals.

In all the above cases, if any operand is NULL, the result is UNKNOWN. If the operands are of different types, special rules apply. These are described in Implicit casts.

Operator IN
The operator IN allows you to test whether a value is equal to one of a list of values.

The result is TRUE if the left operand is not NULL and is equal to one of the right operands. The result is FALSE if the left operand is not NULL and is not equal to any of the right operands, none of which have NULL values. Otherwise the result is UNKNOWN. If the operands are of different types, special rules apply. These are described in Implicit casts.

Operator LIKE
The operator LIKE searches for strings that match a certain pattern.

The result is TRUE if none of the operands is NULL and the source operand (operand_1) matches the pattern operand. The result is FALSE if none of the operands is NULL and the source operand does not match the pattern operand. Otherwise the result is UNKNOWN.

The pattern is specified by a string in which the percent (%) and underscore (_) characters have a special meaning:

  • The underscore character _ matches any single character.
    For example, the following finds matches for IBM and for IGI, but not for International Business Machines or IBM Corp:
    Body.Trade.Company LIKE 'I__'
  • The percent character % matches a string of zero or more characters.
    For example, the following finds matches for IBM, IGI, International Business Machines, and IBM Corp:
    Body.Trade.Company LIKE 'I%'

To use the percent and underscore characters within the expressions that are to be matched, precede the characters with an ESCAPE character, which defaults to the backslash (\) character.

For example, the following predicate finds a match for IBM_Corp.

Body.Trade.Company LIKE 'IBM\_Corp'

You can specify a different escape character by using the ESCAPE clause. For example, you could also specify the previous example like this:

Body.Trade.Company LIKE 'IBM$_Corp' ESCAPE '$'
Operator IS
The operator IS allows you to test whether a value is NULL.

This includes testing values INF, +INF, -INF, NAN (not a number), and NUM in any mixture of case. The alternative forms +INFINITY, -INFINITY, and NUMBER are also accepted.

If applied to non-numeric types, the result is FALSE.

The comparison operator (=) does not allow this because the result of comparing with NULL is NULL. It also allows you to use a more natural English syntax when testing boolean values.

The result is TRUE if the value of the left operand is equal (or not equal if the NOT clause is present) to the specified value (TRUE, FALSE, UNKNOWN, or NULL). Otherwise the result is FALSE.

Operator EXISTS
The operator EXISTS returns a boolean value indicating whether a SELECT function returned one or more values (TRUE) or none (FALSE).
EXISTS(SELECT * FROM something WHERE predicate)

Related concepts
Message flows
Mappings

Related tasks
Developing message flow applications
Developing ESQL

Related reference
Built-in nodes
ESQL
ROW and LIST comparisons
Implicit casts