SQL 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 |
Date and time functions
| Function name | Reference | What it does? |
|---|---|---|
| date(X) | Doc | Returns the current date as YYYY-MM-DD. |
| time(X) | Doc | Returns the current time as HH:MM:SS. |
| datetime(X) | Doc | Returns the current date and time as YYYY-MM-DD HH:MM:SS. |
| julianday(X) | Doc | Returns the Julian day. |
| strftime(X,Y) | Doc | Returns the date formatted as Y. |
| unixepoch(X) | Doc | Returns the Unix epoch as an integer. |
JSON functions
| Function name | Reference | What it does? |
|---|---|---|
| json(X) | Doc | Ensures that X is a valid JSON, and returns a minified version of it. |
| jsonb(X) | Doc | Converts a JSON string to a JSONB object. |
| json_array(X1,X2,…,XN) | Doc | Creates a JSON array of the arguments. |
| json_array_length(X) | Doc | Returns the length of the JSON array X. |
| json_error_position() | Doc | Returns 0 if valid JSON, otherwise the position of the error. |
| json_extract(X,Y) | Doc | Extracts the value of the path Y from the JSON X (e.g., $.a[4].b). |
| json_insert(X,Y,Z) | Doc | Inserts the value Z at the path Y in the JSON X. Multiple Y,Z pairs are supported. |
| json_replace(X,Y,Z) | Doc | Replaces the value at the path Y in the JSON X with Z. Multiple Y,Z pairs are supported. |
| json_set(X,Y,Z) | Doc | Sets the value Z at the path Y in the JSON X, and creates it if it doesn’t exist. Multiple Y,Z pairs are supported. |
| json_object(X1,Y1,X2,Y2,…) | Doc | Creates a JSON object from the key-value pairs(X1:Y1, X2:Y2, ...). |
| json_pretty(X) | Doc | Returns a pretty-printed version of the JSON X. |
| json_remove(X,Y) | Doc | Removes the value at the path Y in the JSON X. Multiple Y pairs are supported. |
| json_type(X) | Doc | Returns the type of the JSON (e.g. object, array, string, number, boolean, null). |
| json_quote(X) | Doc | Returns the SQL value X quoted. |
Additional functions
These functions are not part of SQLite, but they are supported by Anyquery.
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 |
Date and time functions (Anyquery)
| Function name | Usage | Alias |
|---|---|---|
| now() | Returns the current date and time in the local timezone as YYYY-MM-DD HH:MM:SS. | |
| toYYYYMMDDHHMMSS(X) | Converts the date X to a string in the format YYYY-MM-DD HH:MM:SS. | |
| toYYYYMMDD(X) | Converts the date X to a string in the format YYYY-MM-DD. | |
| toYYYYMM(X) | Converts the date X to a string in the format YYYY-MM. | |
| toYYYY(X) | Converts the date X to a string in the format YYYY. | |
| toHH(X) | Converts the date X to a string in the format HH. | |
| toMM(X) | Converts the date X to a string in the format MM. | |
| toSS(X) | Converts the date X to a string in the format SS. | |
| toDateFormatted(X,Y) | Converts the date X to a string using the format Y. See here for the format options. |
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. |