Functions

Here is list of built-in functions. You can add your own via javascript.

General

Syntax Meaning Example
= Equal (as operator) ?where=IDparty=4
!= Not equal (as operator) ?where=IDparty!=4
> Greater than (as operator) ?where=IDparty>4
< Less than (as operator) ?where=IDparty<4
>= Greater than or equal (as operator) ?where=IDparty>=4
<= Less than or equal (as operator) ?where=IDparty<=4
like(string,pattern) Like ?where=like(lastname,X1)&X1=Har%25
null() Generates Null ?where=if(len(foo)=0,null(),foo)
isnull(value) Is null ?where=isnull(comment)
notnull(value) Not null ?where=notnull(comment)
coalesce(value[,…]) Coalesce ?select=coalesce(firstname,surname,IDpresident)
not(expression) Not ?where=not(IDparty=3)
in(expression,value[,…]) In ?where=in(IDparty,1,4)
| Or (as operator) ?where=partyname=democratic|partyname=whig
or(expr,expr) Or (as function) ?where=or(IDparty=1,IDparty=4)
: And (as operator) ?where=partyname=democratic:IDpresident<30
and(expr,expr) And (as function) ?where=and(active=true(),IDpresident<30)
if(cond,value,value) If ?select=if(in(day,6,7),Free,Work)
if(cond,value,[cond,value…[,value]]) If-elseif-else ?select=if(i=1,One,i=2,Two,i=3,Three,More)
nullif(value1,value2) Null if ?select=nullif(name,N)&N=_unnamed_
choose(value,expr,expr) Choose ?select=choose(day,Mon,Thu,Wed)

Mathematical/Operators

Syntax Meaning Example
add(variable[,…]) Add ?select=add(income,income)
sub(variable[,…]) Substract ?select=sub(leftoffice,tookoffice)
mul(variable[,…]) Multiply ?select=mul(income,2)
div(variable[,…]) Divide ?select=div(income,2)
abs(value) Absolute value ?select=abs(number)
exp(value) Exponential value ?select=exp(10)

String

Syntax Meaning Example
lower(string) To lowercase ?select=lower(firstname)
upper(string) To uppercase ?select=upper(firstname)
substr(string,from,count) Substring ?select=substr(firstname,1,1)
md5(variable) MD5 hash value md5(lastname)
concat(field[,…]) Concatenation ?select=concat(firstname,spc,lastname)&spc=%20
trim Remove leading and trailing whitespaces ?select=trim(comment)
ltrim Remove leading whitespaces ?select=ltrim(comment)
rtrim Remove trailing whitespaces ?select=rtrim(comment)
replace(string,search,repl) Replace all occurrences in string of substring search with substring repl ?select=replace(comment,comma,dot)&comma=,&dot=.
lpad Pad the character value left justified to a total width of n character position ?select=lpad(str(id),5,str(0))
rpad Pad the character value right justified to a total width of n character position ?select=rpad(name,15,spc)&spc=%20

Date/Time

Syntax Meaning Example
date(value) Convert argument to date ?select=date(now())
now() Now ?select=now()@current_datetime

Aggregates

Syntax Meaning Example
avg(expression) Average ?select=avg(income)
count(expression) Count ?select=count(lastname)
countif(expression) Count if ?select=countif(IDparty=3)@party3Count
max(expression) Maximum ?select=max(income)
min(expression) Minimum ?select=min(income)
sum(expression) Sum ?select=sum(income)
every(expression) Every ?select=every(income>1000)

Ordering

Syntax Meaning Example
desc(expression) Descending ?order=desc(income)
asc(expression) Descending ?order=asc(lastname)