Scalar functions accept one or more arguments and return a single value per row. They can be used in SELECT columns, WHERE conditions, ORDER BY, GROUP BY, HAVING, CHECK constraints, and DEFAULT values.
Math
| Function | Description |
|---|
abs(X) | Absolute value of X. Returns INTEGER for integer input, REAL for real input, NULL for NULL |
max(X, Y, ...) | Returns the largest argument. Returns NULL if any argument is NULL |
min(X, Y, ...) | Returns the smallest argument. Returns NULL if any argument is NULL |
random() | Returns a pseudo-random 64-bit signed integer |
round(X) | Rounds X to the nearest integer; returns REAL |
round(X, Y) | Rounds X to Y decimal places; returns REAL |
sign(X) | Returns -1, 0, or 1 for negative, zero, or positive X; NULL if X is NULL |
The multi-argument forms of max() and min() are scalar functions. When called with a single argument inside an aggregate query they act as aggregate functions. String
| Function | Description |
|---|
char(X1, X2, ..., XN) | Returns a string built from Unicode code points X1 through XN |
concat(X, ...) | Concatenates all arguments as strings; NULL arguments are skipped |
concat_ws(SEP, X, ...) | Concatenates non-NULL arguments with separator SEP |
format(FORMAT, ...) | Printf-style formatted string |
hex(X) | Uppercase hexadecimal encoding of X |
instr(X, Y) | 1-based position of first occurrence of Y in X, or 0 if not found |
length(X) | Number of characters in a TEXT value, or bytes in a BLOB |
like(X, Y) | 1 if string Y matches LIKE pattern X, else 0 (case-insensitive for ASCII) |
like(X, Y, Z) | Same as like(X, Y) with Z as the escape character |
lower(X) | Lowercase copy of X (ASCII characters only) |
ltrim(X) | Removes leading whitespace from X |
ltrim(X, Y) | Removes leading characters found in Y from X |
octet_length(X) | Length of X in bytes |
printf(FORMAT, ...) | Alias for format() |
quote(X) | SQL literal representation of X (strings single-quoted, BLOBs hex-encoded) |
replace(X, Y, Z) | Returns X with every occurrence of Y replaced by Z |
rtrim(X) | Removes trailing whitespace from X |
rtrim(X, Y) | Removes trailing characters found in Y from X |
soundex(X) | Soundex encoding of string X as a four-character code |
substr(X, Y) | Substring of X starting at 1-based position Y |
substr(X, Y, Z) | Z characters of X starting at position Y |
substring(X, Y) | Alias for substr(X, Y) |
substring(X, Y, Z) | Alias for substr(X, Y, Z) |
trim(X) | Removes leading and trailing whitespace from X |
trim(X, Y) | Removes leading and trailing characters found in Y from X |
unicode(X) | Unicode code point of the first character of X |
upper(X) | Uppercase copy of X (ASCII characters only) |
Conditional
| Function | Description |
|---|
coalesce(X, Y, ...) | Returns the first non-NULL argument |
if(X, Y, Z) | Alias for iif(X, Y, Z) |
ifnull(X, Y) | Returns X if X is not NULL, otherwise Y. Equivalent to coalesce(X, Y) |
iif(X, Y, Z) | Returns Y if X is true (non-zero and non-NULL), otherwise Z |
nullif(X, Y) | Returns NULL if X equals Y, otherwise returns X |
Type
| Function | Description |
|---|
typeof(X) | Storage class of X as a string: "null", "integer", "real", "text", or "blob" |
Pattern Matching
| Function | Description |
|---|
glob(X, Y) | 1 if string Y matches glob pattern X (case-sensitive), else 0 |
like(X, Y) | 1 if string Y matches LIKE pattern X (case-insensitive ASCII), else 0 |
like(X, Y, Z) | Same with Z as the escape character |
Note that glob(X, Y) and like(X, Y) are the functional forms of the Y GLOB X and Y LIKE X operators — argument order is reversed compared to the operator syntax.
Blob
| Function | Description |
|---|
hex(X) | Uppercase hexadecimal representation of blob or integer X |
randomblob(N) | Blob of N pseudo-random bytes |
unhex(X) | Converts hexadecimal string X to a blob; returns NULL for non-hex characters |
unhex(X, Y) | Like unhex(X) but characters in Y are silently ignored in X |
zeroblob(N) | Blob of N zero bytes |
Optimizer Hints
| Function | Description |
|---|
likelihood(X, Y) | Returns X unchanged; hints the query planner X is true with probability Y (0.0–1.0) |
likely(X) | Returns X unchanged; equivalent to likelihood(X, 0.9375) |
unlikely(X) | Returns X unchanged; equivalent to likelihood(X, 0.0625) |
System
| Function | Description |
|---|
changes() | Rows modified by the most recent INSERT, UPDATE, or DELETE |
last_insert_rowid() | Rowid of the most recent successful INSERT on this connection |
load_extension(X) | Loads a Turso-native extension from path X |
sqlite_source_id() | Source identifier string for the SQLite-compatible engine |
sqlite_version() | Returns the compatibility version string "3.42.0" |
total_changes() | Total rows modified since the connection was opened |
changes() and total_changes() have partial support. They correctly count INSERTs but may not account for all UPDATE and DELETE statement results or changes made through triggers.
Function Details
abs(X)
Returns the absolute value of X. Return type matches the input: INTEGER for integers, REAL for floating-point. Returns NULL if X is NULL.
SELECT abs(-42); -- 42
SELECT abs(3.14); -- 3.14
SELECT abs(NULL); -- NULL
char(X1, X2, …, XN)
Returns a string composed of characters with the given Unicode code points. Arguments that are not valid code points are replaced with the replacement character (U+FFFD).
SELECT char(72, 101, 108, 108, 111); -- 'Hello'
SELECT char(9731); -- ☃ (snowman)
SELECT hex(char(0)); -- '00'
coalesce(X, Y, …)
Returns the first argument that is not NULL. Requires at least two arguments.
SELECT coalesce(NULL, NULL, 'fallback'); -- 'fallback'
SELECT coalesce(1, 2, 3); -- 1
concat(X, …) / concat_ws(SEP, X, …)
concat joins all arguments as strings, skipping NULLs. concat_ws inserts the separator between non-NULL arguments.
SELECT concat('Hello', ' ', 'World'); -- 'Hello World'
SELECT concat('a', NULL, 'b'); -- 'ab'
SELECT concat_ws(', ', 'Alice', 'Bob', NULL, 'Carol'); -- 'Alice, Bob, Carol'
printf is an alias.
| Specifier | Description |
|---|
%d | Signed integer |
%f | Floating-point |
%s | String |
%x | Lowercase hexadecimal |
%X | Uppercase hexadecimal |
%o | Octal integer |
%e | Scientific notation |
%g | General floating-point (shortest) |
%% | Literal percent sign |
SELECT format('Hello, %s! You are #%d.', 'Alice', 1);
-- 'Hello, Alice! You are #1.'
SELECT printf('%.2f%%', 99.5);
-- '99.50%'
SELECT format('0x%08X', 255);
-- '0x000000FF'
hex(X) / unhex(X)
hex returns the uppercase hexadecimal representation. For text, it encodes UTF-8 bytes. For blobs, it encodes each byte. For integers, it encodes the value.
unhex converts a hexadecimal string back to a blob. Returns NULL if the input contains non-hex characters.
SELECT hex('ABC'); -- '414243'
SELECT hex(255); -- 'FF'
SELECT unhex('48454C4C4F'); -- blob for 'HELLO'
SELECT unhex('48-45-4C', '-'); -- blob, ignoring dashes
SELECT unhex('ZZZZ'); -- NULL (invalid hex)
iif(X, Y, Z) / if(X, Y, Z)
Returns Y if X is true (non-zero and non-NULL), otherwise Z. if is an alias.
SELECT iif(1 > 0, 'yes', 'no'); -- 'yes'
SELECT iif(NULL, 'yes', 'no'); -- 'no'
SELECT if(10 > 5, 'big', 'small'); -- 'big'
instr(X, Y)
Returns the 1-based position of the first occurrence of Y in X, or 0 if not found. Returns NULL if either argument is NULL.
SELECT instr('Hello World', 'World'); -- 7
SELECT instr('Hello World', 'xyz'); -- 0
length(X) / octet_length(X)
length returns the number of characters in a TEXT value or bytes in a BLOB. octet_length always returns the byte count.
SELECT length('Hello'); -- 5
SELECT length(x'AABBCC'); -- 3
SELECT octet_length('Hello'); -- 5
lower(X) / upper(X)
Returns a lowercase or uppercase copy of X. Only ASCII characters are transformed.
SELECT lower('Hello World'); -- 'hello world'
SELECT upper('Hello World'); -- 'HELLO WORLD'
ltrim(X) / rtrim(X) / trim(X)
Remove characters from the ends of a string. Without a second argument, whitespace is removed. With Y, any characters in Y are removed.
SELECT ltrim(' Hello'); -- 'Hello'
SELECT rtrim('Hello '); -- 'Hello'
SELECT trim(' Hello '); -- 'Hello'
SELECT trim('***Hello***', '*'); -- 'Hello'
nullif(X, Y)
Returns NULL if X equals Y, otherwise returns X. Useful for converting sentinel values to NULL.
SELECT nullif(0, 0); -- NULL
SELECT nullif(5, 0); -- 5
SELECT nullif('N/A', 'N/A'); -- NULL
quote(X)
Returns the SQL literal representation of X. Strings are enclosed in single quotes with proper escaping. BLOBs are encoded as hex literals. NULL returns 'NULL'.
SELECT quote('it''s'); -- '''it''s'''
SELECT quote(42); -- '42'
SELECT quote(NULL); -- 'NULL'
SELECT quote(x'CAFE'); -- 'X''CAFE'''
random() / randomblob(N)
random returns a pseudo-random 64-bit signed integer. randomblob returns a blob of N pseudo-random bytes.
SELECT random(); -- e.g. -4520312828827489743
SELECT hex(randomblob(4)); -- e.g. 'A1B2C3D4'
SELECT abs(random()) % 100; -- random integer 0–99
replace(X, Y, Z)
Returns X with every occurrence of Y replaced by Z. If Y is empty, X is returned unchanged.
SELECT replace('Hello World', 'World', 'Turso'); -- 'Hello Turso'
SELECT replace('2024-01-15', '-', '/'); -- '2024/01/15'
round(X) / round(X, Y)
Rounds X to Y decimal places. Y defaults to 0. Always returns REAL.
SELECT round(3.7); -- 4.0
SELECT round(3.14159, 2); -- 3.14
SELECT round(-2.5); -- -3.0
sign(X)
Returns -1 for negative values, 0 for zero, and 1 for positive values. Returns NULL if X is NULL.
SELECT sign(-42); -- -1
SELECT sign(0); -- 0
SELECT sign(3.14); -- 1
soundex(X)
Returns the Soundex encoding of string X as a four-character code. Useful for fuzzy name matching.
SELECT soundex('Robert'); -- 'R163'
SELECT soundex('Rupert'); -- 'R163'
SELECT soundex('Smith'); -- 'S530'
substr(X, Y) / substr(X, Y, Z)
Returns a substring of X starting at the Y-th character (1-based). If Z is provided, the result is at most Z characters long. Negative Y counts from the end of the string. substring is an alias.
SELECT substr('Hello World', 7); -- 'World'
SELECT substr('Hello World', 1, 5); -- 'Hello'
SELECT substr('Hello World', -5); -- 'World'
SELECT substring('Hello', 2, 3); -- 'ell'
typeof(X)
Returns the storage class of X as a lowercase string.
SELECT typeof(42); -- 'integer'
SELECT typeof(3.14); -- 'real'
SELECT typeof('hello'); -- 'text'
SELECT typeof(NULL); -- 'null'
SELECT typeof(x'CAFE'); -- 'blob'
unicode(X)
Returns the Unicode code point of the first character of X. Returns NULL for NULL or empty string.
SELECT unicode('A'); -- 65
SELECT unicode('Hello'); -- 72 (code point of 'H')
zeroblob(N)
Returns a blob of N zero bytes. Useful for pre-allocating blob storage.
SELECT length(zeroblob(10)); -- 10
SELECT hex(zeroblob(4)); -- '00000000'
last_insert_rowid()
Returns the rowid of the most recent successful INSERT on the current database connection. Returns 0 if no INSERT has been performed.
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO users (name) VALUES ('Alice');
SELECT last_insert_rowid(); -- 1
INSERT INTO users (name) VALUES ('Bob');
SELECT last_insert_rowid(); -- 2
sqlite_version()
Returns the SQLite-compatible version string.
SELECT sqlite_version(); -- '3.42.0'
load_extension(X)
Loads a Turso-native extension from the shared library at path X.
Only Turso-native extensions can be loaded. External SQLite .so/.dll extensions are not supported. The two-argument form load_extension(X, Y) is not supported.
SELECT load_extension('./my_extension');
See Also