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 name | Reference |
---|---|
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 name | Reference |
---|---|
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 name | Usage | Alias |
---|---|---|
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 name | Usage | Alias |
---|---|---|
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 name | Usage | Alias |
---|---|---|
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_float | Returns a random float between 0 and 1. | random_real, random_double, randCanonical |
rand | Returns a random integer up to 4 294 967 295 (2^32 - 1). | random_int |
randn | Returns a random integer between 0 and N. | random_intn |
rand64 | Returns a random integer up to 18 446 744 073 709 551 615 (2^64 - 1). | random_int64 |
randn64 | Returns a random integer between 0 and N. | random_int64n |
Other functions
Function name | Usage | Alias |
---|---|---|
clear_file_cache | Clears the file cache for read_* table functions. | |
clear_plugin_cache(X) | Clears the plugin cache for the plugin X. |