Expressions

SQL expressions are subcomponents of most other commands. Expressions combine one or more values, operators, and SQL functions to produce a result that can used in the enclosing command.

Syntax:

expr binary-op expr |
expr [NOT] { LIKE | GLOB } expr [ESCAPE expr] |
unary-op expr |
( expr ) |
[[database-name .] [table-name .] column-name |
literal-value |
parameter |
function-name ( expr-list | * ) |
expr ISNULL |
expr NOTNULL |
expr [NOT] BETWEEN expr AND expr |
expr [NOT] IN ( value-list ) |
expr [NOT] IN ( select-statement ) |
expr [NOT] IN [database-name .] table-name |
[EXISTS] ( select-statement ) |
CASE [expr] ( WHEN expr THEN expr )+ [ELSE expr] END |
CAST ( expr AS type )
expr COLLATE collation-name

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 operator or any binary operator.

The unary operator [Operator +] is a no-op. It can be applied to strings, numbers, or BLOBs, and it always gives as its result the value of the operand.

Note that there are two variations of the equals and not equals operators. Equals can be either = or ==. The non-equals operator can be either != or <>. The || operator is "concatenate" — it joins together 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. C-style escapes using the backslash character are not supported because they are not standard SQL. BLOB literals are string literals containing hexadecimal data and preceded by a single "x" or "X" character. For example:

X'53514697465'

A literal value can also be the token NULL.

Parameters

A parameter specifies 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 NNN-th 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 name holds a spot for a named parameter with the name AAAA. Named parameters are also numbered. The number assigned is the next unused number. To avoid confusion, it is best to avoid mixing named and numbered parameters.
@AAAA
An "at" sign works exactly like a colon.
$AAAA
A dollar-sign followed by an identifier name also holds a spot for a named parameter with the name AAAA. The identifier name in this case can include one or more occurrences of "::" and a suffix enclosed in "(...)" containing any text at all. This syntax is the form of a variable name in the Tcl programming language.
Note: Parameters that are not assigned values using qdb_stmt_exec() are treated as NULL.

LIKE

The LIKE operator does a pattern-matching comparison. The operand to the right contains the pattern; the left-hand operand 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 (_) in the pattern matches any single character in the string. Any other character matches itself or its lower/upper case equivalent (i.e., case-insensitive matching). (A bug: QDB understands only upper/lower case for 7-bit Latin characters. Hence the LIKE operator is case sensitive for 8-bit iso8859 characters or UTF-8 characters. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.).

If the optional ESCAPE clause is present, then the expression following the ESCAPE keyword must evaluate to a string consisting of 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, respectively. 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 file-globbing 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. The special identifiers refer to the row key only if the CREATE TABLE statement doesn't define a real column with the same name. Row keys act like read-only 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. SELECT * ... doesn't return the row key.

SELECT statements

SELECT statements can appear in expressions as either the right-hand 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 (connected with keywords like UNION or EXCEPT) are allowed. With the EXISTS operator, the columns in the result set of the SELECT are ignored and the expression returns TRUE if one or more rows exist and FALSE if the result set is empty. If no terms in the SELECT expression refer to value in the containing query, then the expression is evaluated once prior to any other processing and the result is reused as necessary. If the SELECT expression does contain variables from the outer query, then the SELECT is reevaluated every time it is needed.

When a SELECT is the right operand of the IN operator, the IN operator returns TRUE if the result of the left operand is any of the values generated by the select. The IN operator may 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, then the first row of the result of the SELECT becomes the value used in the expression. If the SELECT yields more than one result row, all rows after the first are ignored. If the SELECT yields no rows, then the value of the SELECT is NULL.

CAST

A CAST expression changes the datatype of the expr into the type specified by type, where type can be any nonempty type name that is valid for the type in a column definition of a CREATE TABLE statement.

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.

Core Functions

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.
glob(X,Y)
This function is used to implement the X GLOB Y syntax of QDB.
hex(X)
The argument is interpreted as a BLOB. The result is a hexadecimal rendering of the content of that BLOB.
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 row ID of the last row inserted from this connection to the database. This is the same value that would be returned from the qdb_last_insert_rowid().
length(X)
Return the string length of X in characters.
like(X,Y [,Z])
This function is used to implement the X LIKE Y [ESCAPE Z] syntax of SQL. If the optional ESCAPE clause is present, then the user-function is invoked with three arguments. Otherwise, it is invoked with two arguments only.
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 and all characters that appear in Y from the left side of X. If the Y argument is omitted, spaces are removed.
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 two 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 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 if given only a single argument.
nullif(X,Y)
Return the first argument if the arguments are different, otherwise return NULL.
quote(X)
This routine returns a string which is the value of its argument suitable for inclusion into another SQL statement. Strings are surrounded by single quotes with escapes on interior quotes as needed. BLOBs are encoded as hexadecimal literals. The current implementation of VACUUM uses this function. The function is also useful when you're writing triggers to implement undo/redo functionality.
random(*)
Return a random integer between -2147483648 and +2147483647.
randomblob(N)
Return a N-byte BLOB containing pseudo-random bytes. N should 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 used 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 is assumed.
rtrim(X [,Y])
Return a string formed by removing any and all characters that appear in Y from the right side of X. If the Y argument is omitted, spaces are removed.
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 Y-th character and which is Z characters long. The leftmost character of X is number 1. If Y is negative, the first character of the substring is found by counting from the right rather than the left. QDB is configured to support UTF-8, so characters indexes refer to actual UTF-8 characters, not bytes.
trim(X [,Y])
Return a string formed by removing any and all characters that appear in Y from both sides of X. If the Y argument is omitted, spaces are removed.
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 chapter Datatypes in QDB.

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 may not work correctly on UTF-8 strings.

Aggregate Functions

In any aggregate function that takes a single argument, that argument can be preceded by the keyword DISTINCT. In such cases, duplicate elements are filtered before being passed into the aggregate function. For example, the function count(distinct X) will return the number of distinct values of column X instead of the total number of non-NULL values in column X.

avg(X)
Return the average value of all non-NULL X within a group. 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 is not NULL 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 values in the group. The usual sort order is used to determine the maximum.
min(X)
Return the minimum non-NULL value of all values in the 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 non-NULL values in the group. If there are no non-NULL input rows or all values are NULL, then 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 other 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 non-NULL inputs are integers. If any input to sum() is neither an integer or a NULL, then sum() returns a floating point value which might be an approximation to the true sum.

The sum() function throws an "integer overflow" exception if all inputs are integers or NULL and an integer overflow occurs at any point during the computation. The total() function never throws an exception.