Comparison Functions

Comparison operators, predicates and functions are available for all built-in data types.

Comparison operators

All comparison operators are binary operators that return values of type boolean. The usual comparison operators are available:

OperatorDescriptionExampleResult
<less than2 < 5true
>greater than2 > 5false
<=less than or equal to2 <= 5true
>=greater than or equal to2 >= 5false
=equal2 = 5false
<>not equal2 <> 5true

Comparison predicates

The usual comparison predicates are available:

PredicateDescriptionExampleResult
val BETWEEN lb AND ubis val between lb and ub. equivalent to: val >= lb AND val <= ub'db' between 'abc' and 'db'true
val NOT BETWEEN lb AND ubis 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 ubis val between lb and ub after sorting lb and ub'db' between symmetric 'abc' and 'db'true
val NOT BETWEEN SYMMETRIC lb AND ubis val not between lb and ub after sorting lb and ub'db' not between symmetric 'abc' and 'db'false
val IS NULLis val NULL'db' is nullfalse
val IS NOT NULLis val not NULL'db' is not nulltrue

Comparison functions

FunctionReturn typeDescriptionExampleResult
"<"(arg1, arg2)booleanis arg1 less than arg2"<"('aa', 'ab')true
">"(arg1, arg2)booleanis arg1 greater than arg2">"('aa', 'ab')false
"<="(arg1, arg2)booleanis arg1 less than or equal to arg2"<="('aa', 'ab')true
">="(arg1, arg2)booleanis arg1 greater than or equal to arg2">="('aa', 'ab')false
"="(arg1, arg2)booleanis arg1 equal to arg2"="('aa', 'ab')false
"<>"(arg1, arg2)booleanis arg1 not equal to arg2"<>"('aa', 'ab')true
"between"(arg_1 any, arg_2 any, arg_3 any, boolean, boolean, boolean, boolean, boolean)booleanis arg1 between arg2 and arg3"between"('ab', 'aa', 'ac', false, false, false, false, false)true
coalesce(arg1, arg2, ...)same as arg1 or arg2returns 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 arg2returns arg1 when when arg1 IS NOT NULL else returns arg2{fn ifnull(null, 'ams')}'ams'
ifthenelse(boolean arg1, arg2, arg3)same as arg2 or arg3when arg1 expression evaluates to true then return arg2 else arg3ifthenelse(('a' = 'b'), 1, 2)2
isnull(arg1)booleanis arg1 nullisnull('aa')false
nullif(arg1, arg2)same as arg1returns NULL if arg1 = arg2 is true, otherwise returns arg1. Equivalent to: CASE WHEN arg1 = arg2 THEN NULL ELSE arg1 ENDnullif('ams', 'ams')null
"like"(s, pat, escp, isen)booleanmatch 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)booleanmatch 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