SQLite Mobile Client

Additional SQL functions:

• conversions

  • celsius2fahrenheit(real) ➝ real (convert Celsius to Fahrenheit)

  • fahrenheit2celsius(real) ➝ real (convert Fahrenheit to Celsius)

  • kg2lb(real) ➝ real (convert Kilogram to Pound)

  • lb2kg(real) ➝ real (convert Pound to Kilogram)

• strings

  • capitalized(String) ➝ String (e.g. “how are you?” → “How Are You?”)

  • stringLength(String) ➝ int (return the number of characters, not the bytes)

  • stringCharAt(String,Int) ➝ String (return the character at the position)

  • splitPart(String,String,Int) → String (split string by separator and return index part)

• dates

  • date2unix(pattern,datestring) ➝ real // e.g. pattern: ‘yyyy-MM-dd HH:mm:ss’ (case sensitive) or ‘iso8601’

  • date2date(patternIn,datestring,patternOut) ➝ String // e.g. pattern: ‘yyyy-MM-dd HH:mm:ss’ (case sensitive) or ‘iso8601’

  • date2part(patternIn,datestring,partOut) ➝ String // e.g. partOut: year/month/day/hour/minute/second/weekday/weekofyear

  • isoDateTime() ➝ String // return current date time in ISO-8601 format

  • localisoDateTime() ➝ String // return current date time in ISO-8601 format with local timezone

• encodings

  • base64(x) ➝ String (encode binary/blob field to Base64 string)

  • decode_base64(String) ➝ Binary (decode Base64 string to binary/blob)

  • unhex(String) ➝ Binary (decode HEX string to binary/blob)

• encryptions

  • sha1(x) ➝ hexString (calc SHA 1 checksum)

  • sha256(x) ➝ hexString (calc SHA 256 checksum)

  • sha384(x) ➝ hexString (calc SHA 384 checksum)

  • sha512(x) ➝ hexString (calc SHA 512 checksum)

  • aes_cbc_encrypt(String, key:Binary, iv:Binary) ➝ Binary (encrypt text String with AES CBC, iv can be null)

  • aes_cbc_decrypt(Binary, key:Binary, iv:Binary) ➝ String (decrypt encrypted binary with AES CBC, iv can be null)

  • aes_gcm_encrypt(String, key:Binary) ➝ Binary (encrypt text String with AES GCM)

  • aes_gcm_decrypt(Binary, key:Binary) ➝ String (decrypt encrypted binary with AES GCM)

  • chachapoly_encrypt(String, key:Binary) ➝ Binary (encrypt text String with ChaCha20-Poly1305)

  • chachapoly_decrypt(Binary, key:Binary) ➝ String (decrypt encrypted binary with ChaCha20-Poly1305)

  • chachapoly_decrypt(Binary, key:Binary) ➝ String (decrypt encrypted binary with ChaCha20-Poly1305)

• generators

  • random_text(n) ➝ String (generate a random text of length n)

  • random_number(from, to) ➝ Number (generate a random number in the specified range)

  • secure_random() ➝ Number (generate a secure random number)

  • uuid() ➝ String (generate random UUID v4 string value)

  • uuid_v1() ➝ String (generate random UUID v1 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

SQL contrib functions:

• Math: acos, asin, atan, atn2, atan2, acosh, asinh, atanh, difference, degrees, radians, cos, sin, tan, cot, cosh, sinh, tanh, coth, exp, log, log10, power, sign, sqrt, square, ceil, floor, pi, trunc.

• String: replicate, charindex, leftstr, rightstr, ltrim, rtrim, trim, replace, reverse, proper, padl, padr, padc, strfilter.

• Aggregate: stdev, variance, mode, median, lower_quartile, upper_quartile.