Core functions

The following table describes the core scalar SQL functions supported by Devart ODBC Driver for SQLite.

Function	Description
abs(X)	Returns the absolute value of numeric X; NULL if X is NULL; 0.0 if X is text or a BLOB that can’t be converted to a number. If X is -9223372036854775808, this function throws an integer overflow error.
changes()	Returns the number of rows inserted, updated, or deleted by the most recently completed INSERT, UPDATE, or DELETE statement, excluding changes made by lower-level triggers.
char(X1,X2,...,XN)	Returns a string made of characters whose Unicode code points are integers X1 through XN, respectively.
coalesce(X,Y,...)	Returns the first non-NULL argument; NULL if all arguments are NULL. This function requires at least 2 arguments.
concat(X,...)	Returns the concatenation of the string representations of all non-NULL arguments; an empty string if all arguments are NULL.
concat_ws(SEP,X,...)	Returns the concatenation of all non-NULL arguments after SEP, using SEP as the separator; NULL if SEP is NULL; an empty string if all non-separator arguments are NULL.
format(FORMAT,...)	Returns a formatted string (similar to the sqlite3_mprintf() C-language function and the standard C library printf() function); NULL if FORMAT is missing or NULL. This function ignores the %n format, treats the %p format as %X, and the %z format as %s, and uses NULL (mapped to 0, 0.0, or an empty string) for missing arguments.
glob(X,Y)	Is equivalent to the Y GLOB X expression (note the argument order is reversed compared to the GLOB operator form). X is the pattern, and Y is the string. If you override glob() via sqlite3_create_function(), the GLOB operator uses that implementation.
hex(X)	Treats X as a BLOB and returns its uppercase hexadecimal representation. For numeric X, SQLite first converts the number to UTF-8 text and then hex-encodes that text representation (not the numeric binary value).
ifnull(X,Y)	Returns the first non-NULL argument; NULL if both arguments are NULL. This function requires exactly 2 arguments and is equivalent to coalesce(X,Y).
iif(B1,V1,...)	Returns the value (V) paired with the first true Boolean (B); with an odd number of arguments, returns the last argument if all prior Boolean arguments are false; with an even number of arguments and no true Boolean, returns NULL. This function uses short-circuit evaluation.
if(B1,V1,...)	Alias for iif(B1,V1,...).
instr(X,Y)	Returns the 1-based index of the first occurrence of Y in X; 0 if Y isn’t found; NULL if X or Y is NULL. If both X and Y are BLOBs, counts bytes; otherwise, treats them as strings.
last_insert_rowid()	Returns the ROWID of the most recent successful row insert on the current database connection.
length(X)	For text, returns the number of Unicode code points before the first U+0000 character; for BLOBs, returns the byte count; for numbers, returns the length of the text representation of X. This function returns NULL if X is NULL.
like(X,Y)	Implements the Y LIKE X expression (note the argument order is reversed compared to the LIKE operator form). X is the pattern, and Y is the string. This function can be overridden via the sqlite3_create_function() interface (if so, also override like(X,Y,Z) to fully replace LIKE).
like(X,Y,Z)	Implements the Y LIKE X ESCAPE Z expression (note the argument order is reversed compared to the LIKE operator form). X is the pattern, and Y is the string. This function can be overridden via the sqlite3_create_function() interface (if so, also override like(X,Y) to fully replace LIKE).
likelihood(X,Y)	Returns X unchanged. Y must be a floating-point constant in the range between 0.0 and 1.0 (inclusive). This function provides a query-planner hint that X is true with a probability of approximately Y. The unlikely(X) function is shorthand for likelihood(X,0.0625), and likely(X) is likelihood(X,0.9375).
likely(X)	Returns X unchanged. This function provides a query-planner hint that X is usually true. The likely(X) function is equivalent to likelihood(X,0.9375).
load_extension(X)	Loads an extension from shared library X using the default entry point; always returns NULL. This function raises an error if loading or initialization fails, and fails if the extension attempts to modify or delete a SQL function or collating sequence. By default, extension loading is disabled; to enable it, call sqlite3_enable_load_extension().
load_extension(X,Y)	Loads an extension from shared library X using entry point Y; always returns NULL. This function raises an error if loading or initialization fails, and fails if the extension attempts to modify or delete a SQL function or collating sequence. By default, extension loading is disabled; to enable it, call sqlite3_enable_load_extension().
lower(X)	Returns X with ASCII letters converted to lowercase.
ltrim(X)	Removes leading spaces from X.
ltrim(X,Y)	Removes any characters that appear in Y from the left side of X.
max(X,Y,...)	Returns the maximum argument; NULL if any argument is NULL. This function uses the first collating function found (left to right) for all string comparisons; defaults to BINARY if none is found. With one argument, max(X) acts as an aggregate function.
min(X,Y,...)	Returns the minimum argument. This function uses the first collating function found (left to right) for all string comparisons; defaults to BINARY if none is found. With one argument, min(X) acts as an aggregate function.
nullif(X,Y)	Returns X if X and Y differ, otherwise NULL. This function uses the first collating function found (left to right) for all string comparisons; defaults to BINARY if none is found.
octet_length(X)	Returns the byte length of text X in the database encoding; NULL if X is NULL. For BLOBs, this function matches length(X); for numbers, returns the byte length of the text representation of X. The result may differ for the same input text if the database encoding is UTF-16 instead of UTF-8.
printf(FORMAT,...)	Alias for format(FORMAT,...) (kept for backward compatibility).
quote(X)	Returns a SQL literal for X suitable for inclusion in SQL: wraps strings in single quotes and escapes embedded quotes as needed; encodes BLOBs as hexadecimal literals. Text containing NUL is truncated before the first NUL.
random()	Returns a pseudo-random integer between -9223372036854775807 and +9223372036854775807 and deliberately never returns -9223372036854775808 so the result is always safe for abs().
randomblob(N)	Returns an N-byte pseudo-random BLOB; a 1-byte random BLOB if N is less than 1.
replace(X,Y,Z)	Returns X with every occurrence of Y replaced by Z using BINARY comparison. If Y is an empty string, returns X; if Z isn’t a string, casts it to UTF-8 text first.
round(X)	Returns floating-point X rounded to 0 digits to the right of the decimal point.
round(X,Y)	Returns floating-point X rounded to Y digits to the right of the decimal point; if Y is negative, treats it as 0.
rtrim(X)	Removes trailing spaces from X.
rtrim(X,Y)	Removes any characters that appear in Y from the right side of X.
sign(X)	Returns -1, 0, or +1 for negative, zero, or positive numeric X, respectively; NULL if X is NULL or can’t be losslessly converted to a number.
soundex(X)	Returns the Soundex encoding of X; '?000' if X is NULL or has no ASCII letters. This function is available only when SQLite is built with the SQLITE_SOUNDEX compile-time option.
sqlite_compileoption_get(N)	Returns the N-th compile-time option used to build SQLite; NULL if N is out of range.
sqlite_compileoption_used(X)	Returns 1 (true) if compile-time option name X was used to build SQLite, otherwise 0 (false).
sqlite_offset(X)	Returns the byte offset in the database file of the record from which column X would be read (table or index, depending on the query); NULL if X isn’t a column of an ordinary table. This function is available only when SQLite is built with the -DSQLITE_ENABLE_OFFSET_SQL_FUNC compile-time option.
sqlite_source_id()	Returns a string that identifies the SQLite source code build (check-in date and time plus SHA3-256 hash).
sqlite_version()	Returns the SQLite library version string.
substr(X,Y)	Returns all characters or bytes of X starting at index Y (1-based) through the end. Negative Y counts from the right. This function uses UTF-8 character indices for text and byte indices for BLOBs.
substr(X,Y,Z)	Returns Z characters or bytes of X starting at index Y (1-based). Negative Y counts from the right. If Z is negative, this function returns abs(Z) characters or bytes immediately before position Y. This function uses UTF-8 character indices for text and byte indices for BLOBs.
substring(X,Y)	Alias for substr(X,Y).
substring(X,Y,Z)	Alias for substr(X,Y,Z).
total_changes()	Returns the total number of row changes from INSERT, UPDATE, and DELETE statements since the database connection opened.
trim(X)	Removes leading and trailing spaces from X.
trim(X,Y)	Removes any characters that appear in Y from both ends of X.
typeof(X)	Returns the storage class name of expression X: "null", "integer", "real", "text", or "blob".
unhex(X)	Decodes hexadecimal string X into a BLOB; returns NULL if X is NULL, contains non-hex characters, or if the hex digits aren’t in adjacent pairs.
unhex(X,Y)	Decodes hexadecimal string X into a BLOB, ignoring characters in X that match non-hex characters in Y; returns NULL if X or Y is NULL, if X contains other non-hex characters, or if the hex digits aren’t in adjacent pairs.
unicode(X)	Returns the Unicode code point of the first character of string X. The result is unspecified if X isn’t text.
unistr(X)	Interprets backslash escapes in X and returns the corresponding Unicode text (supports \XXXX, \+XXXXXX, \uXXXX, \UXXXXXXXX, and \\; all other characters are literal).
unistr_quote(X)	Returns a SQL literal or constant expression for X, usually identical to quote(X). If X is text containing control characters (U+0001–U+001F), this function escapes those characters and backslashes using JSON-style escapes and wraps the result in unistr(..) (for safe display on devices that interpret ANSI escapes).
unlikely(X)	Returns X unchanged. This function provides a query-planner hint that X is usually false. The unlikely(X) function is equivalent to likelihood(X,0.0625).
upper(X)	Returns X with ASCII letters converted to uppercase.
zeroblob(N)	Returns a BLOB of N bytes of 0x00. This function can be used to reserve space for a BLOB that is written later.

For more information, see the SQLite documentation: Built-In Scalar SQL Functions.