Comparison functions and operators

Comparison functions and operators dinther Thu, 02/06/2020 - 14:00

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:

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


 


 


 

 

Comparison predicates

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


 


 


 


 

 

Comparison functions

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'
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
not_ilike(s, pat) boolean match pcre pattern pat on string s case insensitive. when matched return false else true. not_ilike('abc', '_B%') false
not_ilike(s, pat, escp) boolean match pcre pattern pat on string s case insensitive using escape character escp to escape wildcards. when matched return false else true. not_ilike('a_bc', '_#_B_', '#') false
not_like(s, pat) boolean match pcre pattern pat on string s. when matched return false else true. not_like('abc', '_b%') false
not_like(s, pat, escp) boolean match pcre pattern pat on string s using escape character escp to escape wildcards. when matched return false else true. not_like('a_bc', '_#_b_', '#') false