Functions

Both simple and aggregate functions are supported. A simple function can be used in any expression. Simple functions return a result immediately based on their inputs. Aggregate functions may only be used in a SELECT statement. Aggregate functions compute their result across all rows of the result set.

The functions shown below are available by default.

abs(X) Return the absolute value of argument X.
coalesce(X,Y,...) Return a copy of the first non-NULL argument. If all arguments are NULL then NULL is returned. There must be at least 2 arguments.
compress(X) Compress the binary or string.
encrypt(X,Password) Encrypt the binary or string with a password.
decrypt(X,Password) Decrypt the binary or string with a password.
glob(X,Y) This function is used to implement the "Y GLOB X" syntax of eSQL.
ifnull(X,Y) Return a copy of the first non-NULL argument. If both arguments are NULL then NULL is returned. This behaves the same as coalesce() above.
last_insert_rowid() Return the ROWID of the last row insert from this connection to the database.
length(X) Return the string length of X in characters. If eSQL is configured to support UTF-8, then the number of UTF-8 characters is returned, not the number of bytes.
like(X,Y) This function is used to implement the "Y LIKE X" syntax of SQL.
lower(X) Return a copy of string X will all characters converted to lower case. The C library tolower() routine is used for the conversion, which means that this function might not work correctly on UTF-8 characters.
max(X,Y,...) Return the argument with the maximum value. Arguments may be strings in addition to numbers. The maximum value is determined by the usual sort order. Note that max() is a simple function when it has 2 or more arguments but converts to an aggregate function if given only a single argument.
min(X,Y,...) Return the argument with the minimum value. Arguments may be strings in addition to numbers. The mminimum value is determined by the usual sort order. Note that min() is a simple function when it has 2 or more arguments but converts to an aggregate function if given only a single argument.
nullif(X,Y) Return the first argument if the arguments are different, otherwise return NULL.
random(*) Return a random integer between -2147483648 and +2147483647.
round(X)
round(X,Y)		 Round off the number X to Y digits to the right of the decimal point. If the Y argument is omitted, 0 is assumed.
substr(X,Y,Z) Return a substring of input string X that begins with the Y-th character and which is Z characters long. The left-most character of X is number 1. If Y is negative the the first character of the substring is found by counting from the right rather than the left. If eSQL is configured to support UTF-8, then characters indices refer to actual UTF-8 characters, not bytes.
typeof(X) Return the type of the expression X. The only return values are "numeric" and "text".
uncompress(X) Uncompress the binary or string.
upper(X) Return a copy of input string X converted to all upper-case letters. The implementation of this function uses the C library routine toupper() which means it may not work correctly on UTF-8 strings.

The following aggregate functions are available by default.

avg(X) Return the average value of all X within a group.
count(X)
count(*)		 The first form return a count of the number of times that X is not NULL in a group. The second form (with no argument) returns the total number of rows in the group.
max(X) Return the maximum value of all values in the group. The usual sort order is used to determine the maximum.
min(X) Return the minimum value of all values in the group. The usual sort order is used to determine the minimum.
sum(X) Return the numeric sum of all values in the group.