App Built-in SQL Functions

SQLite database engine provides a number of built-in functions (documentation is provided by official website sqlite.org)

DBCompass for SQLite provides additional sets of functions.

Information

• cell_type(X) ➜ string (returns cell type: integer/float/text/blob/null)

Conversion

• celsius2fahrenheit(real) ➜ real (converts Celsius to Fahrenheit)
• fahrenheit2celsius(real) ➜ real (converts Fahrenheit to Celsius)
• kg2lb(real) ➜ real (converts Kilogram to Pound)
• lb2kg(real) ➜ real (converts Pound to Kilogram)
• currency_format(number) ➜ string (formats number to text using device currency settings)

String

• 🆕 unicode_lower(string) ➜ string (returns the input string with all unicode characters converted to lower case)
• 🆕 unicode_upper(string) ➜ string (returns the input string with all unicode characters converted to upper case)
• 🆕 unicode_reverse(string) ➜ string (returns the input string with all unicode characters reversed)
• 🆕 unaccent(string) ➜ string (e.g. “Hôw Ãrë Yòů?” → “How Are You?”)
• capitalized(string) ➜ string (e.g. “how are you?” → “How Are You?”)
• stringLength(string) ➜ integer (returns the number of unicode characters, not the bytes)
• stringCharAt(string,int) ➜ string (returns the unicode character at the position)
• splitPart(string,string,int) → string (splits string by separator and returns index part)

Other (contrib) String functions are described in the section below.

Date

• date2unix(pattern,datestring) ➜ real

  e.g. pattern: 'yyyy-MM-dd HH:mm:ss' (case sensitive) or 'iso8601'

• date2date(pattern-in,datestring,pattern-out) ➜ string

  e.g. pattern: 'yyyy-MM-dd HH:mm:ss' (case sensitive) or 'iso8601'

• date2part(pattern-in,datestring,part-out) ➜ string

  e.g. part-out possibilities: 'year', 'month', 'day', 'hour', 'minute', 'second', 'weekday', 'weekofyear'

• isoDateTime() ➜ string (returns current date time in ISO-8601 format)

• localisoDateTime() ➜ string (returns current date time in ISO-8601 format with local timezone)
• unix_time() ➜ real (returns current Unix epoc time)

Pattern string is case-sensitive.

Encoding

• base64(X) ➜ string (encodes blob field to Base64 string)
• decode_base64(string) ➜ blob (decodes Base64 string to blob)
• unhex(string) ➜ blob (decodes HEX string to blob)

Hashing

• sha1(X) ➜ hexstring (returns SHA 1 checksum)
• sha256(X) ➜ hexstring (returns SHA 256 checksum)
• sha384(X) ➜ hexstring (returns SHA 384 checksum)
• sha512(X) ➜ hexstring (returns SHA 512 checksum)
• sha3_224(X) ➜ hexstring (returns SHA3-224 checksum)
• sha3_256(X) ➜ hexstring (returns SHA3-256 checksum)
• sha3_384(X) ➜ hexstring (returns SHA3-384 checksum)
• sha3_512(X) ➜ hexstring (returns SHA3-512 checksum)

Encryption

• aes_cbc_encrypt(string, key:blob, iv:blob) ➜ blob (encrypts text string with AES CBC, iv can be null)
• aes_cbc_decrypt(blob, key:blob, iv:blob) ➜ string (decrypts encrypted blob with AES CBC, iv can be null)
• aes_gcm_encrypt(string, key:blob) ➜ blob (encrypts text string with AES GCM)
• aes_gcm_decrypt(blob, key:blob) ➜ string (decrypts encrypted blob with AES GCM)
• chachapoly_encrypt(string, key:blob) ➜ blob (encrypts text string with ChaCha20-Poly1305)
• chachapoly_decrypt(blob, key:blob) ➜ string (decrypts encrypted blob with ChaCha20-Poly1305)
• chachapoly_decrypt(blob, key:blob) ➜ string (decrypts encrypted blob with ChaCha20-Poly1305)

Generator

• random_text(N) ➜ string (generates a random text of length n)
• random_number(from, to) ➜ Number (generates a random number in the specified range)
• secure_random() ➜ Number (generates a secure random number)
• uuid() or uuid_v4() ➜ string (generates UUID v4 string value)
• uuid_v1() ➜ string (generates UUID v1 string value)
• uuid_v7() ➜ string (generates UUID v7 string value)
• cuid() ➜ string (generates CUID v1 string value)
• cuid2() ➜ string (generates CUID v2 string value)

Regex

• regexp(string, regex) ➜ int 0/1 (this can be used also as operator: “S REGEXP R”)
• regex_replace(regex, string, replacement) ➜ string

Image

• create_qr_code_image(string) ➜ blob (generates a PNG QR code image from input text)

Geohash

• geohash_encode(real,real,integer) ➜ string (converts latitude/longitude/precision to a geohash text)
• geohash_decode_latitude(string) ➜ real (converts geohast to latitude)
• geohash_decode_longitude(string) ➜ real (converts geohast to longitude)

XML

• 🆕 filter_xml(string,string) ➜ string (returns XML content given an XML as first parameter and an XPath as second)

Web

• 🆕 encode_url(string) ➜ string (returns a URL-encoded string)
• 🆕 get_url(string) ➜ string (retrieves the URL-encoded string and return the content)

Contrib Functions

Math

• acos(X) ➜ Returns the arccosine of X. The result is in radians

• acosh(X) ➜ Returns the hyperbolic arccosine of X

• asin(X) ➜ Returns the arcsine of X. The result is in radians

• asinh(X) ➜ Returns the hyperbolic arcsine of X

• atan(X) ➜ Returns the arctangent of X. The result is in radians

• atan2(Y,X) ➜ Returns the arctangent of Y/X. The result is in radians. The result is placed into correct quadrant depending on the signs of X and Y

• atanh(X) ➜ Returns the hyperbolic arctangent of X

• 🆕 cbrt(X) ➜ Returns cube root of X

• ceil(X), ceiling(X) ➜ Returns the first representable integer value greater than or equal to X. For positive values of X, this routine rounds away from zero. For negative values of X, this routine rounds toward zero

• cos(X) ➜ Returns the cosine of X. X is in radians

• cosh(X) ➜ Returns the hyperbolic cosine of X

• degrees(X) ➜ Converts value X from radians into degrees

• exp(X) ➜ Computes e (Euler's number, approximately 2.71828182845905) raised to the power X

• floor(X) ➜ Returns the first representable integer value less than or equal to X. For positive numbers, this function rounds toward zero. For negative numbers, this function rounds away from zero

• 🆕 gamma(X) ➜ Returns the gamma function

• log(X), log10(X), log(B,X) ➜ Returns the base-10 logarithm for X. Or, for the two-argument version, return the base-B logarithm of X

• log2(X) ➜ Returns the logarithm base-2 for the number X

• mod(X,Y) ➜ Returns the remainder after dividing X by Y. This is similar to the '%' operator, except that it works for non-integer arguments

• pi() ➜ Returns an approximation for π

• pow(X,Y), power(X,Y) ➜ Computes X raised to the power Y

• radians(X) ➜ Converts X from degrees into radians

• sin(X) ➜ Returns the sine of X. X is in radians

• sinh(X) ➜ Returns the hyperbolic sine of X

• sqrt(X) ➜ Returns the square root of X. NULL is returned if X is negative

• tan(X) ➜ Returns the tangent of X. X is in radians

• tanh(X) ➜ Returns the hyperbolic tangent of X

• trunc(X) ➜ Returns the representable integer in between X and 0 (inclusive) that is furthest away from zero. Or, in other words, return the integer part of X, rounding toward zero. The trunc() function is similar to ceiling(X) and floor(X) except that it always rounds toward zero whereas ceiling(X) and floor(X) round up and down, respectively.

Aggregate

stdev, variance, mode, median, lower_quartile, upper_quartile

String

• replicate ➜ Given a string (s) in the first argument and an integer (n) in the second returns the string that contains s concatenated n times

• charindex ➜ Given 2 input strings (s1,s2) and an integer (n) searches from the nth character for the string s1. Returns the position where the match occurred. Characters are counted from 1. 0 is returned when no match occurs.

• leftstr ➜ Given a string (s) and an integer (n) returns the n leftmost (UTF-8) characters if the string has a length<=n or is NULL this function is NOP

• rightstr ➜ Given a string (s) and an integer (n) returns the n rightmost (UTF-8) characters if the string has a length<=n or is NULL this function is NOP

• reverse ➜ Given a string returns the same string but with the characters in reverse order

• padl ➜ Given an input string (s) and an integer (n) adds spaces at the beginning of s until it has a length of n characters. When s has a length<=n it’s a NOP. padl(NULL) = NULL

• padr ➜ Given an input string (s) and an integer (n) adds spaces at the end of s until it has a length of n characters. When s has a length<=n it’s a NOP. padl(NULL) = NULL

• padc ➜ Given an input string (s) and an integer (n) adds spaces at the end of s and adds spaces at the beginning of s until it has a length of n characters. Tries to add has many characters at the left as at the right. When s has a length<=n it’s a NOP. padl(NULL) = NULL

• strfilter ➜ Given 2 string (s1,s2) returns the string s1 with the characters NOT in s2 removed assumes strings are UTF-8 encoded

IEEE 754 🆕

The ieee754 extension converts a floating point number between its binary64 representation and the M×2E format. In other words in the expression:

F = M × 2E

The ieee754 extension converts between F and (M,E) and back again.

• ieee754(F) ➜ Takes a single floating-point argument as its input and returns a string like 'ieee754(M,E)'

• ieee754(M,E) ➜ Takes the mantissa (M) and exponent (E) values and converts them into the corresponding float value

• ieee754_mantissa(F) ➜ Returns the mantissa of the specified float number

• ieee754_exponent(F) ➜ Returns the exponent of the specified float number

• ieee754_from_blob(B) ➜ Converts a blob into binary64 number

• ieee754_to_blob(F) ➜ Converts a binary64 number into blob

• ieee754_inc(r,N) ➜ Moves the floating point value r by N quantums and return the new values

Decimal 🆕

The decimal extension provides arbitrary-precision decimal arithmetic on numbers stored as text strings. Because the numbers are stored to arbitrary precision and as text, no approximations are needed. Computations can be done exactly.

• decimal(X) ➜ Returns a complete decimal, without the e+NNN at the end

• decimal_exp(X) ➜ Returns the result in exponential notation

• decimal_cmp(X,Y) ➜ Returns negative, zero, or positive if X is less then, equal to, or greater than Y

• decimal_add(X,Y) ➜ Returns the sum of X and Y

• decimal_sub(X,Y) ➜ Returns the difference of X and Y

• decimal_mul(X,Y) ➜ Returns the product of X and Y

• decimal_pow2(N) ➜ Returns the N-th power of 2. N must be an integer