Skip to content

Functions

AnyQuery supports all the functions provided by SQLite as well as some additional functions. The functions are divided into two categories: SQLite functions and additional functions.

SQLite functions

Main functions

Function nameReference
abs(X)Doc
changes()Doc
char(X1,X2,…,XN)Doc
coalesce(X,Y,…)Doc
concat(X,…)Doc
concat_ws(SEP,X,…)Doc
format(FORMAT,…)Doc
glob(X,Y)Doc
hex(X)Doc
ifnull(X,Y)Doc
iif(X,Y,Z)Doc
instr(X,Y)Doc
last_insert_rowid()Doc
length(X)Doc
like(X,Y)Doc
like(X,Y,Z)Doc
likelihood(X,Y)Doc
likely(X)Doc
load_extension(X)Doc
load_extension(X,Y)Doc
lower(X)Doc
ltrim(X)Doc
ltrim(X,Y)Doc
max(X,Y,…)Doc
min(X,Y,…)Doc
nullif(X,Y)Doc
octet_length(X)Doc
printf(FORMAT,…)Doc
quote(X)Doc
random()Doc
randomblob(N)Doc
replace(X,Y,Z)Doc
round(X)Doc
round(X,Y)Doc
rtrim(X)Doc
rtrim(X,Y)Doc
sign(X)Doc
soundex(X)Doc
sqlite_compileoption_get(N)Doc
sqlite_compileoption_used(X)Doc
sqlite_offset(X)Doc
sqlite_source_id()Doc
sqlite_version()Doc
substr(X,Y)Doc
substr(X,Y,Z)Doc
substring(X,Y)Doc
substring(X,Y,Z)Doc
total_changes()Doc
trim(X)Doc
trim(X,Y)Doc
typeof(X)Doc
unhex(X)Doc
unhex(X,Y)Doc
unicode(X)Doc
unlikely(X)Doc
upper(X)Doc
zeroblob(N)Doc

Math functions

Function nameReference
acos(X)Doc
acosh(X)Doc
asin(X)Doc
asinh(X)Doc
atan(X)Doc
atan2(Y,X)Doc
atanh(X)Doc
ceil(X)Doc
ceiling(X)Doc
cos(X)Doc
cosh(X)Doc
degrees(X)Doc
exp(X)Doc
floor(X)Doc
ln(X)Doc
log(B,X)Doc
log(X)Doc
log10(X)Doc
log2(X)Doc
mod(X,Y)Doc
pi()Doc
pow(X,Y)Doc
power(X,Y)Doc
radians(X)Doc
sin(X)Doc
sinh(X)Doc
sqrt(X)Doc
tan(X)Doc
tanh(X)Doc
trunc(X)Doc

Additional functions

String functions

Function nameUsageAlias
ascii(X)Returns the ASCII value of the first character of X.ord
bin(X)Returns the binary representation of X (string or integer).
bit_length(X)Returns the number of bits in X.
chr(X)Returns the character with the ASCII value of X.char
length(X)Returns the length of X.char_length, character_length
elt(X,Y,…)Returns the Y-th element of the list X.
elt_word(X,Y,delim)Returns the Y-th word of the string X.split_part
field(X,Y,…)Returns the index of X in the list Y.
find_in_set(X,Y)Returns the index of X in the list Y (comma-separated).
to_char(X,Y)Converts X to a string using the format Y.
from_base64(X)Decodes the base64-encoded string X.
to_base64(X)Encodes the string X to base64.
to_hex(X)Converts X to a hexadecimal string.
from_hex(X)Converts the hexadecimal string X to a string.
decode(X,Y)Decodes the string X using the encoding Y (base64, hex).
encode(X,Y)Encodes the string X using the encoding Y (base64, hex).
insert(X,Y,Z,N)Inserts the string N into X at position Y with length Z.
locate(X,Y,Z)Returns the position of X in Y starting from Z(optional).position, instr(from SQLite)
lcase(X)Converts X to lowercase.lower
ucase(X)Converts X to uppercase.upper
left(X,Y)Returns the leftmost Y characters of X.
right(X,Y)Returns the rightmost Y characters of X.
load_file(X)Reads the file X and returns its content.
load_file_bytes(X)Reads the file X and returns its content as bytes.
lpad(X,Y,Z)Pads the string X to length Y with Z on the left.
rpad(X,Y,Z)Pads the string X to length Y with Z on the right.
octet_length(X)Returns the length of X in bytes.
to_octal(X)Converts X to an octal string.
regexp_replace(X,Y,Z)Replaces the regular expression Y in X with Z.
regexp_substr(X,Y,Z)Returns the substring of X that matches the regular expression Y.
repeat(X,Y)Repeats the string X Y times.
reverse(X)Reverses the string X.
space(X)Returns a string of X spaces.

URL functions

Function nameUsageAlias
url_encode(X)Encodes the string X to a URL-encoded form.urlEncode
url_decode(X)Decodes the URL-encoded string X.urlDecode
domain(X)Returns the domain of the URL X.urlDomain, url_domain
path(X)Returns the path of the URL X.urlPath, url_path
port(X)Returns the port of the URL X.urlPort, url_port
url_query(X)Returns the query of the URL X.urlQuery
url_parameter(X,Y)Returns the value of the parameter Y in the query of the URL X.urlParameter, extract_url_parameter, extractUrlParameter
protocol(X)Returns the protocol of the URL X.urlProtocol, url_protocol

Crypto functions

Function nameUsageAlias
md5(X)Returns the MD5 hash of the string X.
sha1(X)Returns the SHA-1 hash of the string X.
sha256(X)Returns the SHA-256 hash of the string X.
sha384(X)Returns the SHA-384 hash of the string X.
sha512(X)Returns the SHA-512 hash of the string X.
blake2b(X)Returns the BLAKE2b hash of the string X.
blake2b_384(X)Returns the BLAKE2b-384 hash of the string X.
blake2b_512(X)Returns the BLAKE2b-512 hash of the string X.
random_floatReturns a random float between 0 and 1.random_real, random_double, randCanonical
randReturns a random integer up to 4 294 967 295 (2^32 - 1).random_int
randnReturns a random integer between 0 and N.random_intn
rand64Returns a random integer up to 18 446 744 073 709 551 615 (2^64 - 1).random_int64
randn64Returns a random integer between 0 and N.random_int64n

Other functions

Function nameUsageAlias
clear_file_cacheClears the file cache for read_* table functions.
clear_plugin_cache(X)Clears the plugin cache for the plugin X.