Comparison operators, predicates, and functions are available for all built-in data types.
All comparison operators are binary and return a value of type boolean.
The standard comparison operators are available:
| Operator | Description | Example query | Result |
|---|---|---|---|
< | Less than | SELECT 2 < 5; | true |
> | Greater than | SELECT 2 > 5; | false |
<= | Less than or equal to | SELECT 2 <= 5; | true |
>= | Greater than or equal to | SELECT 2 >= 5; | false |
= | Equal to | SELECT 2 = 5; | false |
<> | Not equal to | SELECT 2 <> 5; | true |
The standard comparison predicates are available:
| Predicate | Description | Example query | Result |
|---|---|---|---|
value BETWEEN lb AND ub | Tests if value is between the lower bound lb and upper bound ub. Equivalent to value >= lb AND value <= ub. | SELECT 'db' BETWEEN 'abc' AND 'db'; | true |
value NOT BETWEEN lb AND ub | Tests if value is not between the lower bound lb and upper bound ub. Equivalent to value < lb OR value > ub. | SELECT 'db' NOT BETWEEN 'abc' AND 'db'; | false |
value BETWEEN SYMMETRIC bound_1 AND bound_2 | Tests if value is between the sorted endpoints bound_1 and bound_2. | SELECT 'db' BETWEEN SYMMETRIC 'abc' AND 'db'; | true |
value NOT BETWEEN SYMMETRIC bound_1 AND bound_2 | Tests if value is not between the sorted endpoints bound_1 and bound_2. | SELECT 'db' NOT BETWEEN SYMMETRIC 'abc' AND 'db'; | false |
value IS NULL | Tests if value is null. | SELECT 'db' IS NULL; | false |
value IS NOT NULL | Tests if value is not null. | SELECT 'db' IS NOT NULL; | true |
| Function | Return type | Description | Example query | Result |
|---|---|---|---|---|
"<"(value_1, value_2) | boolean | Tests if value_1 is less than value_2. | SELECT "<"('aa', 'ab'); | true |
">"(value_1, value_2) | boolean | Tests if value_1 is greater than value_2. | SELECT ">"('aa', 'ab'); | false |
"<="(value_1, value_2) | boolean | Tests if value_1 is less than or equal to value_2. | SELECT "<="('aa', 'ab'); | true |
">="(value_1, value_2) | boolean | Tests if value_1 is greater than or equal to value_2. | SELECT ">="('aa', 'ab'); | false |
"="(value_1, value_2) | boolean | Tests if value_1 is equal to value_2. | SELECT "="('aa', 'ab'); | false |
"<>"(value_1, value_2) | boolean | Tests if value_1 is not equal to value_2. | SELECT "<>"('aa', 'ab'); | true |
"between"(value, lb, ub, boolean, boolean, boolean, boolean, boolean) | boolean | Tests if value is between the lower bound lb and upper bound ub. | SELECT "between"('ab', 'aa', 'ac', false, false, false, false, false); | true |
COALESCE(value_1, value_2, ...) | Same as input | Returns the first non-null value in the list, or NULL if all arguments are null. At least two input parameters are required. | SELECT COALESCE(NULL, 'ac', 'dc'); | "ac" |
{fn IFNULL(value_1, value_2)} | Same as value_1 or value_2 | Returns value_1 if it is not null. Otherwise, the output is value_2. (N.B. this is an ODBC escape sequence). | SELECT {fn IFNULL(NULL, 'ams')}; | "ams" |
IFTHENELSE(boolean, value_1, value_2) | Same as value_1 or value_2 | Returns value_1 if the Boolean input argument is true. Otherwise, the output is value_2. | SELECT IFTHENELSE(('a'='b'), 1, 2); | 2 |
ISNULL(value_1) | boolean | Tests if value_1 is null. | SELECT ISNULL('aa'); | false |
NULLIF(value_1, value_2) | Same as value_1 or value_2 | Returns NULL if value_1 is equal to value_2. Otherwise, the output is value_1. Equivalent to CASE WHEN value_1 = value_2 THEN NULL ELSE value_1 END | SELECT NULLIF('ams', 'ams'); | NULL |
"like"(str_value, pattern, escp, isen) | boolean | Matches PCRE pattern pattern on string value str_value using escape character escp to escape wildcards, and flag isen for case insensitive matches. When matched, returns true else false. | SELECT "like"('ab', 'a%', '#', false); | true |
NOT_LIKE(str_value, pattern, escp, isen) | boolean | Matches PCRE pattern pattern on string value str_value using escape character escp to escape wildcards, and flag isen for case insensitive matches. When matched, returns false else true. | SELECT NOT_LIKE('a_bc', '_%b%', '#', false); | false |