SQL expressions are subcomponents of commands.
Expressions combine one or more values, operators, and SQL functions to produce a result that can be used in the enclosing command.
Description:
SQL expressions are made up of several smaller components, including literals for specifying exact values,
operators for comparing values and performing pattern matching, and functions for calculating and modifying values.
These smaller components evaluate to a single result that's used in a broader SQL command.
Operators
QDB understands the following binary operators, in order from highest to lowest precedence:

* / %
+ 
<< >> & 
< <= > >=
= == != <> IN
AND
OR
The supported unary prefix operators are:
 + ! ~ NOT
The COLLATE operator can be thought of as a unary postfix operator. The COLLATE
operator has the highest precedence—it always binds more tightly than any prefix unary or any binary operator.
The unary operator + is a noop. It can be applied to strings, numbers, or BLOBs, and it always gives
the value of the operand as its result.
Note that there are two variants of the equals and not equals operators. Equals can be either = or
==, and nonequals can be either != or <>.
The  operator is “concatenate”—it joins the two strings of its operands.
The % operator outputs the remainder of its left operand modulo its right operand.
The result of any binary operator is a numeric value, except for the  concatenation operator,
which gives a string result.
Literal values
A literal value is an integer number or a floating point number. Scientific notation is supported.
The “.” character is always used as the decimal point even if the locale setting specifies “,” for
this role—the use of “,” for the decimal point would result in syntactic ambiguity.
A string constant is formed by enclosing the string in single quotation marks ('). A single quotation mark within the
string can be encoded by putting two single quotes in a row, as in Pascal. Cstyle escapes using the backslash character
are not supported because they are not standard SQL.
BLOB literals are string literals containing hexadecimal data and are preceded by a single “x” or “X” character,
as follows: X'53514697465'
A literal value can also be the token NULL.
Parameters
A parameter provides a placeholder in the expression for a literal value that is filled in at runtime using
qdb_stmt_exec(). Parameters can take several forms:
 ?NNN
 A question mark followed by a number, NNN, holds a spot for the NNNth parameter.
NNN must be between 1 and 999.
 ?
 A question mark that is not followed by a number holds a spot for the next unused parameter.
 :AAAA
 A colon followed by an identifier, AAAA, holds a spot for a parameter named
AAAA. Named parameters are given a number by QDB, which assigns the next unused number.
To avoid confusion, it is best to avoid mixing named and explicitly numbered parameters.
 @AAAA
 An “at” sign works exactly like a colon.
 $AAAA
 A dollarsign followed by an identifier also holds a spot for a named parameter.
In this case, the identifier can include one or more occurrences of “::” and a suffix enclosed in
“(...)” containing any text that you want. This syntax is the form of a variable name in the Tcl language.
Note: Parameters not assigned values with qdb_stmt_exec() are treated as NULL.
LIKE
The LIKE operator does a patternmatching comparison. The operand to the right contains the pattern;
the operand to the left contains the string to match against the pattern.
A percent symbol (%) in the pattern matches any sequence of zero or more characters in the string.
An underscore (_) matches any single character in the string. Any other character matches itself or its lower/upper
case equivalent (i.e., caseinsensitive matching).
Note: QDB understands only upper/lower case for 7bit Latin characters. Hence, the LIKE operator is case
sensitive for 8bit iso8859 characters or UTF8 characters. For example, the expression
'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is
FALSE.
If the optional ESCAPE clause is present, the expression following the ESCAPE keyword
must evaluate to a string containing a single character. This character may be used in the LIKE pattern
to include literal percent or underscore characters. The escape character followed by a percent symbol, underscore, or
itself matches a literal percent symbol, underscore, or escape character in the string.
The infix LIKE operator is implemented by calling the user function
like(X,Y).
GLOB
The GLOB operator is similar to LIKE, but uses the UNIX fileglobbing syntax for
its wildcards. Also, GLOB is case sensitive, unlike LIKE.
Both GLOB and LIKE may be preceded by the NOT keyword to invert
the sense of the test. The infix GLOB operator is implemented by calling the user function
glob(X,Y) and can be modified by overriding that function.
Column names
A column name can be any of the names defined in the CREATE TABLE statement or one of the following
special identifiers: ROWID, OID, or _ROWID_.
These special identifiers all describe the unique random integer key (the row key) associated with every row of
every table, but refer to that key only if the CREATE TABLE statement doesn't define a column with the
same name.
Row keys act like readonly columns. A row key can be used anywhere a regular column can be used, except that you cannot
change the value of a row key in an UPDATE or INSERT statement.
Note that the SELECT * ... command does not return the row key.
SELECT statements in SQL expressions
SELECT statements can appear in SQL expressions as either the right operand of the IN
operator, as a scalar quantity, or as the operand of an EXISTS operator.
As a scalar quantity or the operand of an IN operator, the SELECT should have only
a single column in its result. Compound SELECTs (which are connected with keywords like
UNION or EXCEPT) are allowed. With the EXISTS operator,
the columns in the result set are ignored and the expression returns either
TRUE if one or more rows exist, or FALSE if the result set is empty.
If no terms in the SELECT statement refer to the value in the containing query, the statement is
evaluated once prior to any other processing and the result is reused as necessary. If the statement does contain
variables from the outer query, the SELECT is reevaluated every time it is needed.
When a SELECT is the right operand of the IN operator, this operator returns
TRUE if the result of the left operand is any of the values generated by the SELECT.
The IN operator can be preceded by the NOT keyword to invert the sense of the test.
When a SELECT appears within an expression but is not the right operand of an IN
operator, the first row of the result becomes the value used in the expression. If the SELECT yields
more than one result row, all rows after the first are ignored. Finally, if the SELECT yields no rows,
the value of the statement is NULL.
CAST
The CAST function changes the datatype of an expression into a specified type, which can be any
nonempty name that is valid as the type in a column defined by a CREATE TABLE statement.
Functions
QDB supports simple and aggregate functions.
Simple functions can be used in any expression, and return a result immediately based on their inputs.
Aggregate functions can be used only in SELECT statements. These functions compute their result
across all rows of the result set.
Core Functions
The following core functions are available by default:
 abs(X)

Return the absolute value of argument X.
 coalesce(X,Y,...)

Return a copy of the first nonNULL argument. If all arguments are NULL,
then NULL is returned. There must be at least two arguments.
 glob(X,Y)

Perform casesensitive pattern matching, with support for Unix wildcards.
This function implements the X GLOB Y syntax of SQL expressions supported by QDB.
 hex(X)

Generate a hexadecimal rendering of the given argument, which is interpreted as a BLOB.
 ifnull(X,Y)

Return a copy of the first nonNULL argument. If both arguments are NULL, then
NULL is returned. This behaves the same as coalesce() above.
 last_insert_rowid()

Return the row ID of the last row inserted from this connection to the database. This is the same value that
would be returned from qdb_last_insert_rowid().
 length(X)

Return the string length of X in characters.
 like(X,Y[,Z])

Perform caseinsensitive pattern matching.
This function implements the X LIKE Y [ESCAPE Z] component of SQL expressions.
If the optional ESCAPE clause is present, the user function is invoked with three arguments;
otherwise, it's invoked with two arguments.
 lower(X)

Return a copy of string X with all characters converted to lower case.
 ltrim(X[,Y])

Return a string formed by removing any characters that appear in Y from the left side of
X. If the Y argument is omitted, spaces are removed from the left side.
 max(X,Y,...)

Return the argument with the maximum value. Arguments may be strings or numbers. The maximum value is determined
by the usual sort order. Note that max() is a simple function when it has two or more arguments
but converts to an aggregate function when given only one argument.
 min(X,Y,...)

Return the argument with the minimum value. Arguments may be strings or numbers. The minimum value is determined
by the usual sort order. Note that min() is a simple function when it has two or more arguments
but converts to an aggregate function when given only one argument.
 nullif(X,Y)

Return the first argument if the arguments are different, otherwise return NULL.
 quote(X)

Return a string that's the value of X in a form suitable for inclusion in another
SQL statement. Strings are surrounded by single quotes and have escapes on any interior quotes.
BLOBs are encoded as hexadecimal literals. This function is used by the implementation of VACUUM
and is helpful if you're writing triggers to implement undo/redo functionality.
 random(*)

Return a random integer between 2147483648 ((2^{31})) and +2147483647 (2^{31} 1).
 randomblob(N)

Return an Nbyte BLOB containing pseudorandom bytes. N must be a
postive integer.
 replace(X,Y,Z)

Return a string formed by substituting string Z for every occurrence of string
Y in string X. The BINARY collating sequence is user for
comparisons.
 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 digits after the decimal is assumed.
 rtrim(X[,Y])

Return a string formed by removing any characters that appear in Y from the right side
of X. If the Y argument is omitted, spaces are removed from the
right side.
 soundex(X)

Compute the soundex encoding of the string X.
The string ?000 is returned if the argument is NULL.
 sqlite_version()

Return the version string for the SQLite library that is running. Example: 3.7.9
 substr(X,Y,Z)

Return a substring of input string X that begins with the Yth character
and is Z characters long. The leftmost character of X is character number 1.
If Y is negative, the first character of the substring is found by counting from the right
rather than the left.
Note: QDB supports UTF8, so character indexes refer to UTF8 characters, not bytes.
 trim(X[,Y])

Return a string formed by removing any characters that appear in Y from both sides of
X. If the Y argument is omitted, spaces are removed from both sides.
 typeof(X)

Return the type of the expression X. The possible return values are:
 null
 integer
 real
 text
 blob
 QDB's type handling is explained in the Datatypes in QDB chapter.
 upper(X)

Return a copy of input string X converted to all uppercase letters. The implementation of this
function uses the C library routine toupper(), which means it might not work correctly on UTF8 strings.
Aggregate Functions
In aggregate functions that take a single argument, the argument can be preceded by the keyword
DISTINCT. In such cases, duplicate elements are filtered before being passed to the function.
For example, the SQL expression count(distinct X) returns the number of distinct values of the column
that X evaluates to instead of the total number of nonNULL values in that column.
The following aggregate functions are available:
 avg(X)

Return the average value of all nonNULL values of X within a group specified
in the broader SQL expression. String and BLOB values that don't look like numbers are interpreted as 0.
The result of avg() is always a floating point value, even if all inputs are integers.
 count([X])

The first form, which takes the argument X, returns the number of times that
X evaluates to nonNULL in a group.
The second form, which takes no arguments, returns the total number of rows in the group.
 max(X)

Return the maximum value of all nonNULL values of X within a group.
The usual sort order is used to determine the maximum.
 min(X)

Return the minimum value of all nonNULL values of X within a group.
The usual sort order is used to determine the minimum. NULL is returned only if all values
in the group are NULL.
 sum(X), total(X)

Return the numeric sum of all nonNULL values of X within a group.
If there are no nonNULL input rows or all values are NULL, sum()
returns NULL and total() returns 0.0.
NULL is not normally a helpful result for the sum of no rows, but the SQL standard requires it and
most SQL database engines implement sum() that way, so QDB does it in the same way to be
compatible. The total() function is provided as a convenient way to work around this design
problem in the SQL language.
 The result of total() is always a floating point value. The result of sum()
is an integer value if all nonNULL inputs are integers. If any input to sum()
is neither an integer nor a NULL, sum() returns a floating point value that
might be an approximation to the true sum.
 The sum() function throws an “integer overflow” exception if all inputs are either integers
or NULL and an integer overflow occurs during the computation. The total()
function never throws an exception.