This topic describes ESQL's complex comparison operators (predicates). For information about ESQL's simple comparison operators, see ESQL simple comparison operators.
This operator exists in two forms, SYMMETRIC and ASYMMETRIC (which is 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.
If the operands are of different types, special rules apply. These are described in Implicit casts.
EXISTS(SELECT * FROM something WHERE predicate)
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.
The primary purpose of the operator IS is to test whether a value is NULL. The comparison operator (=) does not allow this because the result of comparing anything with NULL is NULL.
IS also allows you to test for the Boolean values TRUE and FALSE, and the testing of decimal values for special values. These are denoted by INF, +INF, -INF, NAN (not a number), and NUM (a valid number) 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 result is TRUE if none of the operands is NULL and the source operand 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:
Body.Trade.Company LIKE 'I__'
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 '$'