ESQL complex comparison operators

ESQL supports several operators for complex comparison tasks.

If you want to use ESQL to perform a simple comparison, see ESQL simple comparison operators.

Operator BETWEEN

Use the BETWEEN operator to test whether a value lies between two boundary values.

BETWEEN operator

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 useful only when the boundary condition expressions are literals.

If the operands are of different types, special rules apply. These rules are described in Implicit casts.

Operator EXISTS

EXISTS operator

The operator EXISTS returns a Boolean value that indicates whether a SELECT function returned one or more values (TRUE) or none (FALSE).

EXISTS(SELECT * FROM something WHERE predicate)

Operator IN

Use the operator IN to test whether a value is equal to one of a list of values.

IN operator

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 one or more 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 rules are described in Implicit casts.

operand_1 must evaluate to a scalar value. operand_2 can be a sequence of expressions that return scalars of types comparable with operand_1 or it can be a single expression that returns a LIST. If the LIST is one that is returned from a SELECT function, there can be only a single column in the SelectClause and each ROW in that column is compared with operand_1 for equality.

The following examples show a sequence of expressions.

Result1 is set to TRUE because 30 is not present in the given list:
```
SET OutputRoot.XMLNSC.Top.Result1 = 30 NOT IN(34, 42, 45)
```
Result2 is set to TRUE if the value of var1 is found in var2, var3 or var4:
```
SET OutputRoot.XMLNSC.Top.Result2 = var1 IN(var2, var3, var4);
```

The following examples show a list containing a single column.

Result3 is set to TRUE if 42 is found in the result set returned from the SELECT statement:

SET OutputRoot.XMLNSC.Top.Result3 = 42 IN(
    SELECT A FROM InputRoot.XMLNSC.Top.a[] AS A);

Result4 is set to TRUE because 42 is present in the given list:

SET OutputRoot.XMLNSC.Top.Result4 = 42 IN(
    LIST{34,36,37,38,39,40,41,42,43,44});

Result5 is set to TRUE if var1 is found in one of the repeating 'test' elements or its immediate children, if present:
```
SET OutputRoot.XMLNSC.Top.Result5 = var1 IN(
    InputRoot.XMLNSC.Top.test[]);
```

Operator IS

Use the operator IS to test whether an expression has returned a special value.

IS operator

The primary purpose of the IS operator is to test whether a value is NULL. You cannot use the comparison operator (=) to test for a NULL value, because the result of comparing any value with NULL is NULL.

You can also use the IS operand to test for the Boolean values TRUE and FALSE, and to test decimal values for special values. These values are denoted by INF, +INF, -INF, NAN (not a number), and NUM (a valid number) in upper, lower, or mixed case. The alternative forms +INFINITY, -INFINITY, and NUMBER are also accepted.

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

Operator LIKE

Use the LIKE operator to search for strings that match a certain pattern.

LIKE operator

The result is TRUE if none of the operands are NULL and the source operand matches the pattern operand. The result is FALSE if none of the operands are 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 a 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 phrase 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 in this way:

Body.Trade.Company LIKE 'IBM$_Corp' ESCAPE '$'

Operator SINGULAR

SINGULAR operator

The operator SINGULAR returns a Boolean value of TRUE if the list has exactly one element, otherwise it returns FALSE.