Thursday, September 24, 2009

SQL As Understood By SQLite

Core Functions

The core functions shown below are available by default. Date & Time functions and aggregate functions are documented separately. An application may define additional functions written in C and added to the database engine using the sqlite3_create_function() API.
abs(X)
Return the absolute value of the numeric argument X. Return NULL if X is NULL. Return 0.0 if X is not a numeric value.
changes()
Return the number of database rows that were changed or inserted or deleted by the most recently complete SQL. This is a wrapper around the sqlite3_changes() C/C++ function.
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 "Y GLOB X" syntax of SQLite. Note that the X and Y arguments are reversed in the glob() function relative to the infix GLOB operator. The sqlite3_create_function() interface can be used to override this function and thereby change the operation of the GLOB operator.
ifnull(X,Y)
Return a copy of the first non-NULL argument. If both arguments are NULL then NULL is returned. The ifnull() functions and coalesce() with two arguments are interchangeable.
hex(X)
The argument is interpreted as a BLOB. The result is a hexadecimal rendering of the content of that blob.
last_insert_rowid()
Return the ROWID of the last row insert from this connection to the database. This is the same value that would be returned from the sqlite3_last_insert_rowid() API function.
length(X)
Return the string length of X in characters if X is a string, or in bytes if X is a blob.
like(X,Y)
like(X,Y,Z)
This function is used to implement the "Y LIKE X [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. Note that the X and Y parameters are reversed in the like() function relative to the infix LIKE operator. The sqlite3_create_function() interface can be used to override this function and thereby change the operation of the LIKE operator. When doing this, it may be important to override both the two and three argument versions of the like() function. Otherwise, different code may be called to implement the LIKE operator depending on whether or not an ESCAPE clause was specified.
load_extension(X)
load_extension(X,Y)
Load SQLite extensions out of the shared library file named X using the entry point Y. The result is a NULL. If Y is omitted then the default entry point of sqlite3_extension_init is used. This function raises an exception if the extension fails to load or initialize correctly. This function will fail if the extension attempts to modify or delete a SQL function or collating sequence. The extension can add new functions or collating sequences, but cannot modify or delete existing functions or collating sequences because those functions and/or collating sequences might be used elsewhere in the currently running SQL statement. To load an extension that changes or deletes functions or collating sequences, use the sqlite3_load_extension() C-language API.
lower(X)
Return a copy of string X with all ASCII characters converted to lower case. The default built-in lower() function works for ASCII characters only. To do case conversions on non-ASCII characters, load the ICU extension.
ltrim(X)
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. Or return NULL if any argument is NULL. 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. 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.
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. SQLite uses this function internally in its implementation of VACUUM so if this function is overloading to provide incompatible behavior, the VACUUM command will likely cease to work.
random()
Return a pseudo-random integer between -9223372036854775808 and +9223372036854775807.
randomblob(N)
Return an N-byte blob containing pseudo-random bytes. N should be a postive integer. Hint: applications can generate globally unique identifiers using this function together with hex() and/or lower() like this:
hex(randomblob(16))
lower(hex(randomblob(16)))
replace(X,Y,Z)
Return a string formed by substituting string Z for every occurrance of string Y in string X. The BINARY collating sequence is used for comparisons. If Y is an empty string then return X unchanged.
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.
rtrim(X)
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. This function is omitted from SQLite by default. It is only available the -DSQLITE_SOUNDEX=1 compiler option is used when SQLite is built.
sqlite_source_id()
Return a string that identifies the specific version of the source code that was used to build the SQLite library. The return string begins with the date and time that the source code was checked in and is follows by an SHA1 hash that uniquely identifies the source tree. This function is an SQL wrapper around the sqlite3_sourceid() C interface.
sqlite_version()
Return the version string for the SQLite library that is running. Example: "3.5.9". This function is an SQL wrapper around the sqlite3_libversion() C-interface.
substr(X,Y,Z)
substr(X,Y)
Return a substring of input string X that begins with the Y-th character and which is Z characters long. If Z is omitted then all character through the end of the string are returned. 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 X is string then characters indices refer to actual UTF-8 characters. If X is a BLOB then the indices refer to bytes.
total_changes()
Return the number of row changes caused by INSERT, UPDATE or DELETE statements since the current database connection was opened. This function is a wrapper around the sqlite3_total_changes() C/C++ interface.
trim(X)
trim(X,Y)
Return a string formed by removing any and all characters that appear in Y from both ends of X. If the Y argument is omitted, spaces are removed.
typeof(X)
Return the datatype of the expression X. The only return values are "null", "integer", "real", "text", and "blob".
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 non-ASCII UTF-8 strings.
zeroblob(N)
Return a BLOB consisting of N bytes of 0x00. SQLite manages these zeroblobs very efficiently. Zeroblobs can be used to reserve space for a BLOB that is later written using incremental BLOB I/O. This SQL function is implemented using the sqlite3_result_zeroblob() routine from the C/C++ interface.

No comments: