Using SQLite

Literal Expressions

The simplest type of expression is a literal, or inline value. These are specific values that are expressed directly in the SQL command. SQLite supports a number of literal forms, including one for each major datatype.

Each supported datatype has a specific literal representation. This allows the expression processor to understand the desired datatype as well as the specific value.

NULL

A NULL is represented by the bare keyword NULL.

NULL

Integer

An integer number is represented by a bare sequence of numeric digits. All integers must be given in base-10. A prefix of zero digits does not represent octal numbers, nor are hexadecimal integers supported. No magnitude separators (such as a comma after the thousands digit) are allowed. The number can be prefaced with a + or - to represent the sign of the number:

8632   -- Eight thousand, six hundred, thirty-two
0032   -- Thirty-two
-5     -- Negative five
+17    -- Seventeen

Real or floating-point

A real number is represented by a bare sequence of numeric digits, followed by a period (decimal point), followed by another sequence of numeric digits. Either of the number sequences on the left or right of the decimal point can be omitted, but not both. SQLite always uses a period for the decimal point, regardless of internationalization settings. The number can be prefaced with a + or - to represent the sign of the number.

The initial set of numbers can be followed by an optional exponent, used to represent scientific notation. This is represented with the letter E (upper- or lowercase) followed by an optional + or -, followed by a sequence of numeric digits. The number does not need to be normalized.

If an exponent is included and the number group to the right of the decimal point is omitted, the decimal point may also be omitted. This is the only situation when the decimal point may be omitted:

32.4              --    32.4
-535.             --  -535.0
.43               --     0.43
4.5e+1            --    45.0
78.34E-5          --     0.0007834
7e2               --   700.0

Text or string

A text value is represented by a string of characters enclosed in single quotes ( ' ' ). Double quotes ( " " ) are used to enclose identifiers, and should not be used to enclose literal values. To escape a single quote inside of a text literal, use two single quote characters in a row. The backslash character ( \ ), used in C and many other languages as an escape character, is not considered special by SQL and cannot be used to escape quote characters within text literals. A zero-length text value is not the same as a NULL:

'Jim has a dog.'         Jim has a dog.
'Jim''s dog is big.'     Jim's dog is big.
'C:\data\'               C:\data\
''                       (zero-length text value)

BLOB

A BLOB value is represented as an X (upper- or lowercase) followed by a text literal consisting of hexadecimal characters (0–9, A–F, a–f). Two hex characters are required for each full byte, so there must be an even number of characters. The length of the BLOB (in bytes) will be the number of hex characters divided by two. Like text values, the byte values are given in order:

X'7c'
X'8A26E855'
x''

Be aware that these are input formats that are recognized by the SQL command parser. They are not necessarily the output format used to display the values. The display format is up to the SQL environment, such as the sqlite3 utility. To output values as valid SQL literals, see the quote() SQL function.

In addition to explicit literals, SQLite supports three named literals that can be used to insert the current date or time. When an expression is evaluated, these named tags will be converted into literal text expressions of the appropriate value. Supported tags are:

CURRENT_TIME: A text value in the format HH:MM:SS.
CURRENT_DATE: A text value in the format YYYY-MM-DD.
CURRENT_TIMESTAMP: A text value in the format YYYY-MM-DD HH:MM:SS.

All times and dates are in UTC, not your local time zone.

Lastly, in any place that SQLite will accept a literal expression, it will also accept a statement parameter. Statement parameters are placeholders, similar to external variables. When using the C API, a statement can be prepared, values can then be bound to the parameters, and the statement can be executed. The statement can be reset, new values can be bound, and the statement can be executed again. Statement parameters allow frequently reused statements (such as many INSERT statements) to be prepared once and used over and over again by simply binding new values to the statement parameters. There are a number of performance and security benefits from this process, but it is only applicable for those using a programming interface to SQLite.

SQLite supports the following syntax for statement parameters:

?: A single question mark character. SQLite will automatically assign an index to each parameter.
?numb: A single question mark character followed by a number. The number will become the parameter index. The same index may be used more than once.
:name: A single colon character followed by a name. The API provides a way to look up the index based off the name. The same name may be used more than once.
@name: A single at (@) character followed by a name. The API provides a way to look up the index based off the name. The same name may be used more than once. This variation is nonstandard.
$name: A single dollar sign character followed by a name. The API provides a way to look up the index based off the name. The same name may be used more than once. This variation is nonstandard and understands a special syntax that is designed to be used with Tcl variables.

Statement parameters can only be used to replace literals. They cannot be used to replace identifiers, such as table or column names. See the section Bound Parameters for more details on how to use statement parameters with the C API.

Previous Chapter

D. SQLite SQL Expression Reference

Next Chapter

Logic Representations

Table of Contents for Using SQLite

Literal Expressions

Table of Contents for
Using SQLite