Scalar Functions

Scalar functions accept one or more arguments and return a single value. They can be used anywhere an expression is valid: SELECT columns, WHERE conditions, ORDER BY, GROUP BY, HAVING, CHECK constraints, and DEFAULT values.

Function Reference

Math Functions

Function	Description
`abs(X)`	Absolute value of X. Returns INTEGER if X is INTEGER, REAL if X is REAL, NULL if X is NULL
`max(X, Y, ...)`	Returns the argument with the maximum value
`min(X, Y, ...)`	Returns the argument with the minimum value
`random()`	Returns a random 64-bit signed integer
`round(X)`	Rounds X to the nearest integer
`round(X, Y)`	Rounds X to Y decimal places
`sign(X)`	Returns -1, 0, or 1 for negative, zero, or positive X

String Functions

Function	Description
`char(X1, X2, ..., XN)`	Returns a string composed of characters with Unicode code points X1 through XN
`chr(X)`	Alias for `char(X)`. PostgreSQL/SQL-standard spelling
`concat(X, ...)`	Concatenates all arguments as strings. NULL arguments are skipped
`concat_ws(SEP, X, ...)`	Concatenates arguments with separator SEP. NULL arguments are skipped
`format(FORMAT, ...)`	Returns a formatted string using printf-style format specifiers
`hex(X)`	Returns the uppercase hexadecimal representation of X
`instr(X, Y)`	Returns the 1-based position of the first occurrence of Y in X, or 0 if not found
`strpos(X, Y)`	Alias for `instr(X, Y)`. PostgreSQL spelling, identical argument order
`length(X)`	Returns the string length in characters, or blob length in bytes
`char_length(X)`	Alias for `length(X)`. SQL-standard short form
`character_length(X)`	Alias for `length(X)`. SQL-standard long form
`lower(X)`	Returns a lowercase copy of string X
`lpad(X, N)` / `lpad(X, N, F)`	Pads X on the left to length N using F (defaults to a single space)
`ltrim(X)`	Removes leading whitespace from X
`ltrim(X, Y)`	Removes leading characters found in Y from X
`octet_length(X)`	Returns the length of X in bytes
`printf(FORMAT, ...)`	Alias for `format()`. Returns a formatted string
`quote(X)`	Returns the SQL literal representation of X
`repeat(X, N)`	Returns X concatenated N times. `N <= 0` produces an empty string
`replace(X, Y, Z)`	Returns X with every occurrence of Y replaced by Z
`reverse(X)`	Returns the characters of string X in reverse order. Alias for `string_reverse(X)`
`rpad(X, N)` / `rpad(X, N, F)`	Pads X on the right to length N using F (defaults to a single space)
`rtrim(X)`	Removes trailing whitespace from X
`rtrim(X, Y)`	Removes trailing characters found in Y from X
`soundex(X)`	Returns the Soundex encoding of string X
`string_reverse(X)`	Returns the characters of string X in reverse order
`substr(X, Y)`	Returns the substring of X starting at position Y (1-based)
`substr(X, Y, Z)`	Returns Z characters from 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
`btrim(X)` / `btrim(X, Y)`	Alias for `trim(X)` / `trim(X, Y)`. PostgreSQL/Oracle spelling
`unhex(X)`	Converts hexadecimal string X to a blob. Returns NULL if X contains non-hex characters
`unhex(X, Y)`	Like `unhex(X)`, but characters in Y are silently ignored in X
`unicode(X)`	Returns the Unicode code point of the first character of string X
`unistr(X)`	Evaluates PostgreSQL-style Unicode escapes in X (`\XXXX`, `\+XXXXXX`, `\uXXXX`, `\UXXXXXXXX`, `\\`)
`unistr_quote(X)`	Returns X as a quoted string literal with non-ASCII characters written as `unistr` escapes
`upper(X)`	Returns an uppercase copy of string X
`zeroblob(N)`	Returns a blob consisting of N zero bytes

Conditional Functions

Function	Description
`coalesce(X, Y, ...)`	Returns the first non-NULL argument. Returns NULL if all arguments are NULL
`ifnull(X, Y)`	Returns X if X is not NULL, otherwise returns Y. Equivalent to `coalesce(X, Y)`
`iif(X, Y, Z)`	Returns Y if X is true (non-zero), otherwise returns Z
`if(X, Y, Z)`	Alias for `iif(X, Y, Z)`
`nullif(X, Y)`	Returns NULL if X equals Y, otherwise returns X

Type Functions

Function	Description
`typeof(X)`	Returns the type of X as a string: `"null"`, `"integer"`, `"real"`, `"text"`, or `"blob"`

Pattern Matching Functions

Function	Description
`glob(X, Y)`	Returns 1 if string Y matches glob pattern X, 0 otherwise. Case-sensitive
`like(X, Y)`	Returns 1 if string Y matches LIKE pattern X, 0 otherwise. Case-insensitive for ASCII
`like(X, Y, Z)`	Like `like(X, Y)` but uses Z as the escape character

Optimizer Hints

Function	Description
`likelihood(X, Y)`	Returns X unchanged. Hints to the query planner that X is true with probability Y (0.0 to 1.0)
`likely(X)`	Returns X unchanged. Equivalent to `likelihood(X, 0.9375)`
`unlikely(X)`	Returns X unchanged. Equivalent to `likelihood(X, 0.0625)`

Blob Functions

Function	Description
`hex(X)`	Returns the uppercase hexadecimal representation of blob or integer X
`randomblob(N)`	Returns a blob of N bytes filled with pseudo-random data
`unhex(X)`	Converts hexadecimal string X to a blob
`zeroblob(N)`	Returns a blob of N zero bytes

System Functions

Function	Description
`last_insert_rowid()`	Returns the rowid of the most recent successful INSERT on the same database connection
`changes()`	Returns the number of rows modified by the most recent INSERT, UPDATE, or DELETE
`total_changes()`	Returns the total number of rows modified since the database connection was opened
`sqlite_version()`	Returns the version string `"3.42.0"`
`sqlite_source_id()`	Returns the source identifier string for the SQLite-compatible engine
`turso_version()`	Returns the Turso engine version string (e.g. `"0.7.0"`)
`load_extension(X)`	Loads a Turso-native extension from the shared library at path X

Detailed Descriptions and Examples

abs(X)

Returns the absolute value of X. The return type matches the input: INTEGER for integer inputs, REAL for floating-point inputs. Returns NULL if X is NULL. Returns INTEGER for a text value that looks like an integer.

SELECT abs(-42);       -- 42
SELECT abs(3.14);      -- 3.14
SELECT abs(0);         -- 0
SELECT abs(NULL);      -- NULL

char(X1, X2, …, XN) and chr(X)

char returns a string composed of characters having the Unicode code points X1 through XN. Arguments that are not valid code points are replaced with the Unicode replacement character (U+FFFD). chr is a single-argument alias provided for PostgreSQL and SQL-standard compatibility. It is identical to char(X).

SELECT char(72, 101, 108, 108, 111);   -- 'Hello'
SELECT char(9731);                       -- snowman character
SELECT hex(char(0));                     -- '00'
SELECT chr(65);                          -- 'A'

coalesce(X, Y, …)

Returns the first argument that is not NULL. If all arguments are NULL, returns NULL. Requires at least two arguments.

SELECT coalesce(NULL, NULL, 'fallback');   -- 'fallback'
SELECT coalesce(1, 2, 3);                  -- 1
SELECT coalesce(NULL, 42);                 -- 42

concat(X, …) and 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'
SELECT concat_ws('-', 2024, 1, 15);            -- '2024-1-15'

format(FORMAT, …) and printf(FORMAT, …)

Returns a formatted string using printf-style format specifiers. printf is an alias for format.

Specifier	Description
`%d`	Signed integer
`%f`	Floating-point
`%s`	String
`%x`	Lowercase hexadecimal integer
`%X`	Uppercase hexadecimal integer
`%o`	Octal integer
`%e`	Scientific notation
`%g`	General floating-point (shortest representation)
`%%`	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'

glob(X, Y)

Returns 1 if string Y matches the glob pattern X, and 0 otherwise. Glob matching is case-sensitive and uses * for any sequence of characters, ? for any single character, and [...] for character classes.

SELECT glob('*.txt', 'readme.txt');   -- 1
SELECT glob('*.TXT', 'readme.txt');   -- 0 (case-sensitive)
SELECT glob('H?llo', 'Hello');        -- 1

The glob(X, Y) function is the functional form of the Y GLOB X operator. Note the reversed argument order compared to the operator syntax.

hex(X) and unhex(X)

hex returns the uppercase hexadecimal representation of its argument. For text, it returns the hex encoding of the UTF-8 bytes. For blobs, it encodes each byte. For integers, it returns the hex of the value. unhex converts a hexadecimal string back to a blob. Returns NULL if the input contains non-hex characters, unless a second argument specifies characters to ignore.

SELECT hex('ABC');             -- '414243'
SELECT hex(255);               -- 'FF'
SELECT hex(x'CAFE');           -- 'CAFE'

SELECT unhex('48454C4C4F');    -- x'48454C4C4F' (blob for 'HELLO')
SELECT unhex('48-45-4C', '-'); -- x'48454C' (ignoring dashes)
SELECT unhex('ZZZZ');          -- NULL (invalid hex)

iif(X, Y, Z) and if(X, Y, Z)

Returns Y if X is true (non-zero and non-NULL), otherwise returns Z. if is an alias for iif.

SELECT iif(1 > 0, 'yes', 'no');       -- 'yes'
SELECT iif(NULL, 'yes', 'no');         -- 'no'
SELECT if(10 > 5, 'big', 'small');     -- 'big'

instr(X, Y) and strpos(X, Y)

Returns the 1-based position of the first occurrence of string Y in string X. Returns 0 if Y is not found in X. If either argument is NULL, returns NULL. strpos is a PostgreSQL-compatible alias with the same argument order.

SELECT instr('Hello World', 'World');   -- 7
SELECT instr('Hello World', 'xyz');     -- 0
SELECT instr('abcabc', 'bc');           -- 2
SELECT strpos('Hello World', 'World');  -- 7

length(X), char_length(X), character_length(X), and octet_length(X)

length returns the number of characters in a text value, or the number of bytes in a blob value. For NULL, returns NULL. For numeric values, returns the length of the text representation. char_length and character_length are SQL-standard aliases for length, accepted for compatibility with PostgreSQL and other engines. octet_length always returns the length in bytes, regardless of type.

SELECT length('Hello');             -- 5
SELECT length(x'AABBCC');           -- 3 (bytes for blob)
SELECT length(12345);               -- 5 (text representation)
SELECT char_length('café');         -- 4 (counts characters, not bytes)
SELECT character_length('café');    -- 4
SELECT octet_length('Hello');       -- 5 (ASCII, 1 byte per char)
SELECT octet_length('café');        -- 5 (é is 2 bytes in UTF-8)

like(X, Y) and like(X, Y, Z)

Returns 1 if string Y matches LIKE pattern X, and 0 otherwise. % matches any sequence of characters, _ matches any single character. Matching is case-insensitive for ASCII characters. The optional third argument Z specifies an escape character.

SELECT like('%ello%', 'Hello World');     -- 1
SELECT like('H_llo', 'Hello');            -- 1
SELECT like('10\%%', '10% discount', '\'); -- 1 (escaped %)

The like(X, Y) function is the functional form of the Y LIKE X operator. Note the reversed argument order compared to the operator syntax.

lower(X), upper(X)

lower returns a copy of string X with all ASCII characters converted to lowercase. upper converts to uppercase.

SELECT lower('Hello World');   -- 'hello world'
SELECT upper('Hello World');   -- 'HELLO WORLD'

ltrim(X), rtrim(X), trim(X), btrim(X)

These functions remove characters from the ends of a string. Without a second argument, they remove whitespace. With a second argument Y, they remove any characters present in the string Y. btrim is a PostgreSQL/Oracle-compatible alias for trim (both the one- and two-argument forms).

-- Whitespace trimming
SELECT ltrim('   Hello');          -- 'Hello'
SELECT rtrim('Hello   ');          -- 'Hello'
SELECT trim('   Hello   ');        -- 'Hello'
SELECT btrim('   Hello   ');       -- 'Hello'

-- Character trimming
SELECT ltrim('xxxHello', 'x');       -- 'Hello'
SELECT rtrim('Helloyyy', 'y');       -- 'Hello'
SELECT trim('***Hello***', '*');     -- 'Hello'
SELECT trim('abcHelloabc', 'abc');   -- 'Hello' (removes any of a, b, or c)
SELECT btrim('xyzHelloxyz', 'xyz');  -- 'Hello'

max(X, Y, …) and min(X, Y, …)

The multi-argument forms of max and min return the largest or smallest argument, respectively. Arguments are compared using the standard SQLite comparison rules. If any argument is NULL, the result is NULL.

SELECT max(1, 5, 3);          -- 5
SELECT min(1, 5, 3);          -- 1
SELECT max('apple', 'banana'); -- 'banana'
SELECT min(10, NULL, 3);      -- NULL

The multi-argument max() and min() are scalar functions. When called with a single argument inside an aggregate query (e.g., SELECT max(salary) FROM employees), they act as aggregate functions.

nullif(X, Y)

Returns NULL if X equals Y, otherwise returns X. This is useful for converting sentinel values to NULL.

SELECT nullif(0, 0);        -- NULL
SELECT nullif(5, 0);        -- 5
SELECT nullif('N/A', 'N/A'); -- NULL
SELECT nullif('hello', '');  -- 'hello'

quote(X)

Returns the text of an SQL literal that represents the value X. Strings are enclosed in single quotes with escaping. BLOBs are encoded as hex literals. NULL returns the string 'NULL'. Numbers are returned as-is.

SELECT quote('it''s');       -- '''it''s'''
SELECT quote(42);            -- '42'
SELECT quote(NULL);          -- 'NULL'
SELECT quote(x'CAFE');       -- 'X''CAFE'''

random() and 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' (4 random bytes)
SELECT abs(random()) % 100; -- random number between 0 and 99

lpad(X, N) and lpad(X, N, F)

Left-pads the string X with the fill string F until the result has exactly N characters. F defaults to a single space. If X is already at least N characters long, it is truncated from the right to length N. F is cycled when it is more than one character long. Returns NULL if any argument is NULL. If F is an empty string, the input is returned unchanged. Lengths are counted in Unicode characters, not bytes, so multi-byte input is padded correctly.

SELECT lpad('abc', 6);              -- '   abc'
SELECT lpad('abc', 6, 'xy');        -- 'xyxabc'
SELECT lpad('abcdef', 3);           -- 'abc'   (truncated from the right)
SELECT lpad('aéc', 4);              -- ' aéc'  (character count, not bytes)
SELECT lpad('abc', 10, '');         -- 'abc'   (empty fill)
SELECT lpad(NULL, 5);               -- NULL

rpad(X, N) and rpad(X, N, F)

Right-pads the string X with the fill string F until the result has exactly N characters. F defaults to a single space. Truncation, fill cycling, NULL propagation, and empty-fill handling are identical to lpad (see the section above).

SELECT rpad('abc', 6);              -- 'abc   '
SELECT rpad('abc', 6, 'xy');        -- 'abcxyx'
SELECT rpad('abcdef', 3);           -- 'abc'   (truncated from the right)
SELECT rpad('aéc', 4);              -- 'aéc '

repeat(X, N)

Returns the string X concatenated with itself N times. Returns an empty string when N <= 0. Returns NULL if either argument is NULL. Non-text inputs are converted to their text representation first.

SELECT repeat('ab', 3);      -- 'ababab'
SELECT repeat('x', 0);       -- ''
SELECT repeat('x', -1);      -- ''
SELECT repeat('', 5);        -- ''
SELECT repeat(NULL, 3);      -- NULL

replace(X, Y, Z)

Returns a copy of string X with every occurrence of string Y replaced by string Z. If Y is empty, X is returned unchanged.

SELECT replace('Hello World', 'World', 'Turso');   -- 'Hello Turso'
SELECT replace('aabbcc', 'bb', 'XX');               -- 'aaXXcc'
SELECT replace('2024-01-15', '-', '/');              -- '2024/01/15'

reverse(X) and string_reverse(X)

Returns string X with its characters in reverse order. Reversal is done on Unicode characters, so multi-byte characters are preserved intact. Returns NULL if X is NULL. Non-text inputs are converted to their text representation first. reverse is the PostgreSQL (since version 17), MySQL, and Oracle spelling; string_reverse is the canonical Turso name. The two are interchangeable.

SELECT reverse('hello');         -- 'olleh'
SELECT reverse('');              -- ''
SELECT reverse('café');          -- 'éfac'
SELECT string_reverse('abc');    -- 'cba'
SELECT reverse(NULL);            -- NULL

round(X) and round(X, Y)

Rounds X to Y decimal places. If Y is omitted, it defaults to 0. The return type is always REAL.

SELECT round(3.7);         -- 4.0
SELECT round(3.14159, 2);  -- 3.14
SELECT round(2.5);         -- 3.0
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
SELECT sign(NULL);   -- NULL

substr(X, Y) and substr(X, Y, Z)

Returns a substring of X starting at the Y-th character (1-based). If Z is provided, the substring 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: "null", "integer", "real", "text", or "blob".

SELECT typeof(42);         -- 'integer'
SELECT typeof(3.14);       -- 'real'
SELECT typeof('hello');    -- 'text'
SELECT typeof(NULL);       -- 'null'
SELECT typeof(x'CAFE');    -- 'blob'
SELECT typeof(1 + 1.0);   -- 'real'

unicode(X)

Returns the Unicode code point of the first character of string X. Returns NULL if X is NULL or an empty string.

SELECT unicode('A');     -- 65
SELECT unicode('Hello'); -- 72 (code point of 'H')

soundex(X)

Returns the Soundex encoding of string X as a four-character code. Soundex encodes a string based on how it sounds in English, which is useful for fuzzy name matching.

SELECT soundex('Robert');    -- 'R163'
SELECT soundex('Rupert');    -- 'R163'
SELECT soundex('Smith');     -- 'S530'
SELECT soundex('Smythe');    -- 'S530'

zeroblob(N)

Returns a blob consisting of N zero bytes (0x00). 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

changes() and total_changes()

changes returns the number of rows modified by the most recent INSERT, UPDATE, or DELETE statement. total_changes returns the total number of rows modified since the database connection was opened.

CREATE TABLE t (x INTEGER);
INSERT INTO t VALUES (1), (2), (3);
SELECT changes();        -- 3

DELETE FROM t WHERE x > 1;
SELECT changes();        -- 2
SELECT total_changes();  -- 5 (3 inserted + 2 deleted)

sqlite_version()

Returns the SQLite-compatible version string.

SELECT sqlite_version();  -- '3.42.0'

turso_version()

Returns the version string of the Turso engine.

SELECT turso_version();  -- e.g. '0.7.0'

load_extension(X)

Loads a Turso-native extension from the shared library at path X.

SELECT load_extension('./my_extension');

Extension loading must be enabled on the database connection. See the Extensions documentation for details on building and loading extensions.

​Scalar Functions

​Function Reference

​Math Functions

​String Functions

​Conditional Functions

​Type Functions

​Pattern Matching Functions

​Optimizer Hints

​Blob Functions

​System Functions

​Detailed Descriptions and Examples

​abs(X)

​char(X1, X2, …, XN) and chr(X)

​coalesce(X, Y, …)

​concat(X, …) and concat_ws(SEP, X, …)

​format(FORMAT, …) and printf(FORMAT, …)

​glob(X, Y)

​hex(X) and unhex(X)

​iif(X, Y, Z) and if(X, Y, Z)

​instr(X, Y) and strpos(X, Y)

​length(X), char_length(X), character_length(X), and octet_length(X)

​like(X, Y) and like(X, Y, Z)

​lower(X), upper(X)

​ltrim(X), rtrim(X), trim(X), btrim(X)

​max(X, Y, …) and min(X, Y, …)

​nullif(X, Y)

​quote(X)

​random() and randomblob(N)

​lpad(X, N) and lpad(X, N, F)

​rpad(X, N) and rpad(X, N, F)

​repeat(X, N)

​replace(X, Y, Z)

​reverse(X) and string_reverse(X)

​round(X) and round(X, Y)

​sign(X)

​substr(X, Y) and substr(X, Y, Z)

​typeof(X)

​unicode(X)

​soundex(X)

​zeroblob(N)

​last_insert_rowid()

​changes() and total_changes()

​sqlite_version()

​turso_version()

​load_extension(X)

​See Also

Scalar Functions

Function Reference

Math Functions

String Functions

Conditional Functions

Type Functions

Pattern Matching Functions

Optimizer Hints

Blob Functions

System Functions

Detailed Descriptions and Examples

abs(X)

char(X1, X2, …, XN) and chr(X)

coalesce(X, Y, …)

concat(X, …) and concat_ws(SEP, X, …)

format(FORMAT, …) and printf(FORMAT, …)

glob(X, Y)

hex(X) and unhex(X)

iif(X, Y, Z) and if(X, Y, Z)

instr(X, Y) and strpos(X, Y)

length(X), char_length(X), character_length(X), and octet_length(X)

like(X, Y) and like(X, Y, Z)

lower(X), upper(X)

ltrim(X), rtrim(X), trim(X), btrim(X)

max(X, Y, …) and min(X, Y, …)

nullif(X, Y)

quote(X)

random() and randomblob(N)

lpad(X, N) and lpad(X, N, F)

rpad(X, N) and rpad(X, N, F)

repeat(X, N)

replace(X, Y, Z)

reverse(X) and string_reverse(X)

round(X) and round(X, Y)

sign(X)

substr(X, Y) and substr(X, Y, Z)

typeof(X)

unicode(X)

soundex(X)

zeroblob(N)

last_insert_rowid()

changes() and total_changes()

sqlite_version()

turso_version()

load_extension(X)

See Also