Expressions

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.

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

The unary operator + is a no-op. 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 non-equals 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. 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 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 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, 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 dollar-sign 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 pattern-matching 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., case-insensitive matching).

Note: 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, 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 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, but refer to that key only if the CREATE TABLE statement doesn't define a 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. 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 non-NULL argument. If all arguments are NULL, then NULL is returned. There must be at least two arguments.

glob(X,Y)

Perform case-sensitive 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 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 qdb_last_insert_rowid().

length(X)

Return the string length of X in characters.

like(X,Y[,Z])

Perform case-insensitive 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³¹)) and +2147483647 (2³¹ -1).

randomblob(N)

Return an N-byte BLOB containing pseudo-random 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 Y-th 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 UTF-8, so character indexes refer to UTF-8 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 UTF-8 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 non-NULL values in that column.

The following aggregate functions are available:

avg(X): Return the average value of all non-NULL 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 non-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 non-NULL values of X within a group. The usual sort order is used to determine the maximum.
min(X): Return the minimum value of all non-NULL 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 non-NULL values of X within a group. If there are no non-NULL 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 non-NULL 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.