Comparison operators, predicates and functions are available for all built-in data types.
All comparison operators are binary operators that return values of type boolean. The usual comparison operators are available:
| Operator | Description | Example | Result |
|---|---|---|---|
| < | less than | 2 < 5 | true |
| > | greater than | 2 > 5 | false |
| <= | less than or equal to | 2 <= 5 | true |
| >= | greater than or equal to | 2 >= 5 | false |
| = | equal | 2 = 5 | false |
| <> | not equal | 2 <> 5 | true |
The usual comparison predicates are available:
| Predicate | Description | Example | Result |
|---|---|---|---|
| val BETWEEN lb AND ub | is val between lb and ub. equivalent to: val >= lb AND val <= ub | 'db' between 'abc' and 'db' | true |
| val NOT BETWEEN lb AND ub | is val not between lb and ub. equivalent to: val < lb OR val > ub | 'db' not between 'abc' and 'db' | false |
| val BETWEEN SYMMETRIC lb AND ub | is val between lb and ub after sorting lb and ub | 'db' between symmetric 'abc' and 'db' | true |
| val NOT BETWEEN SYMMETRIC lb AND ub | is val not between lb and ub after sorting lb and ub | 'db' not between symmetric 'abc' and 'db' | false |
| val IS NULL | is val NULL | 'db' is null | false |
| val IS NOT NULL | is val not NULL | 'db' is not null | true |
| Function | Return type | Description | Example | Result |
|---|---|---|---|---|
| "<"(arg1, arg2) | boolean | is arg1 less than arg2 | "<"('aa', 'ab') | true |
| ">"(arg1, arg2) | boolean | is arg1 greater than arg2 | ">"('aa', 'ab') | false |
| "<="(arg1, arg2) | boolean | is arg1 less than or equal to arg2 | "<="('aa', 'ab') | true |
| ">="(arg1, arg2) | boolean | is arg1 greater than or equal to arg2 | ">="('aa', 'ab') | false |
| "="(arg1, arg2) | boolean | is arg1 equal to arg2 | "="('aa', 'ab') | false |
| "<>"(arg1, arg2) | boolean | is arg1 not equal to arg2 | "<>"('aa', 'ab') | true |
| "between"(arg_1 any, arg_2 any, arg_3 any, boolean, boolean, boolean, boolean, boolean) | boolean | is arg1 between arg2 and arg3 | "between"('ab', 'aa', 'ac', false, false, false, false, false) | true |
| coalesce(arg1, arg2, ...) | same as arg1 or arg2 | returns the first non-NULL value in the list, or NULL if all args are NULL. At least two parameters must be passed. | coalesce(null, 'ac', 'dc') | 'ac' |
| {fn IFNULL(arg1, arg2)} | same as arg1 or arg2 | returns arg1 when when arg1 IS NOT NULL else returns arg2 | {fn ifnull(null, 'ams')} | 'ams' |
| ifthenelse(boolean arg1, arg2, arg3) | same as arg2 or arg3 | when arg1 expression evaluates to true then return arg2 else arg3 | ifthenelse(('a' = 'b'), 1, 2) | 2 |
| isnull(arg1) | boolean | is arg1 null | isnull('aa') | false |
| nullif(arg1, arg2) | same as arg1 | returns NULL if arg1 = arg2 is true, otherwise returns arg1. Equivalent to: CASE WHEN arg1 = arg2 THEN NULL ELSE arg1 END | nullif('ams', 'ams') | null |
| "like"(s, pat, escp, isen) | boolean | match pcre pattern 'pat' on string 's' using escape character 'escp' to escape wildcards, and flag 'isen' for case insensitive matches. when matched return true else false. | "like"('ab', 'a%', '#', false) | true |
| not_like(s, pat, escp, isen) | boolean | match pcre pattern 'pat' on string 's' using escape character 'escp' to escape wildcards, and flag 'isen' for case insensitive matches. when matched return false else true. | not_like('a_bc', '_%b%', '#', false) | false |