-
-
Notifications
You must be signed in to change notification settings - Fork 653
Built‐in SQL functions
Paweł Salawa edited this page Jun 4, 2026
·
5 revisions
SQLiteStudio defines several SQL functions in databases that it connects to, so you can use them as long as you execute queries from withing SQLiteStudio.
Functions registered by SQLiteStudio are available only from SQLiteStudio. They will not be available in other applications connecting to the database, unless those applications define such functions by themself
| Function | Description |
|---|---|
| debug(msg) | Prints the message passed as argument in the Letos's status pane. It could be useful for example for debugging TRIGGERS. It implicitly accepts more than 1 argument and joins them all with a whitespace. You can simply SELECT debug('Value of new.column_x:', new.column_x); in TRIGGER's body. Just remember to remove it for production database, because there would probably be no debug(msg) function. |
| regexp(pattern, arg) | Matches arg against pattern, where the pattern is a regular expression. This also makes the REGEXP operator available in SQL queries. Returns 1 on match, or 0 when arg was not matched. |
| sqlfile(file) | Reads given file as text file containing SQL queries and executes all those queries. Returns first column of first row from results of the last query executed from the file. |
| readfile(file) | Reads given file as binary file and returns bytes from it. |
| writefile(file, data) | Writes given data bytes into given file. Returns number of bytes actually written. |
| langs() | Returns list of scripting languages available in SQLiteStudio at the moment (this depends on what scripting language plugins are currently loaded). |
| script(language, code) | Evaluates given code using scripting plugin that supports given language and returns result of the evaluation. Use langs() function to learn supported languages. |
| charsets() | Returns list of charsets supported by SQLiteStudio (to be used for example in arguments for import() function) |
| import_formats() | Returns list of importing formats supported by SQLiteStudio (depends on import plugins being loaded) |
| import_options(format) | Returns list of currently used importing settings for certain format (the format must be one of formats returned from import_formats()). Each setting in a separate line. Each line is a setting_name=setting_value
|
| import(file, format, table, charset, options) | Executes importing process using file for input, format for choosing import plugin (must be one of values returned from import_formats()). The import is done into the table. If table does not exists, it will be created. The charset is optional and must be one of values returned from charsets() (for example 'UTF-8'). It defaults to UTF-8. The options is optional and has to be in the same format as returned from import_options() (which is one option per line, each line is option_name=value), although it's okay to provide only a subset of options - then the rest of settings will remain. |
| html_escape(string) | Replaces HTML-specific characters in the string with their HTML escape codes and returns modified string. For example '<' will be replaced with '<'. |
| url_encode(string) | Replaces URL-specific characters with their percent escape codes and returns modified string. For example '/' will be replaced with '%2F'. |
| url_decode(string) | Replaces percent escape codes of the URL with their actual characters and returns modified string. For example '%2F' will be replaced with '/'. |
| base64_encode(data) | Encodes given bytes with BASE64 encoding and returns BASE64 string. |
| base64_decode(data) | Decodes given bytes from BASE64 encoding and returns decoded bytes. |
| md4_bin(data) | Calculates MD4 hash function for given data and returns calculated value as bytes. |
| md4(data) | Calculates MD4 hash function for given data and returns calculated value as hexadecimal string. |
| md5_bin(data) | Calculates MD5 hash function for given data and returns calculated value as bytes. |
| md5(data) | Calculates MD5 hash function for given data and returns calculated value as hexadecimal string. |
| sha1(data) | Calculates SHA-1 hash function for given data and returns calculated value as bytes. |
| sha224(data) | Calculates SHA-224 hash function for given data and returns calculated value as bytes. |
| sha256(data) | Calculates SHA-256 hash function for given data and returns calculated value as bytes. |
| sha384(data) | Calculates SHA-384 hash function for given data and returns calculated value as bytes. |
| sha512(data) | Calculates SHA-512 hash function for given data and returns calculated value as bytes. |
| sha3_224(data) | Calculates SHA3-224 hash function for given data and returns calculated value as bytes. |
| sha3_256(data) | Calculates SHA3-256 hash function for given data and returns calculated value as bytes. |
| sha3_384(data) | Calculates SHA3-384 hash function for given data and returns calculated value as bytes. |
| sha3_512(data) | Calculates SHA3-512 hash function for given data and returns calculated value as bytes. |