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

String Functions

Conditional Functions

Type Functions

Pattern Matching Functions

Optimizer Hints

Blob Functions

System Functions

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.

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

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 %)

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

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'

load_extension(X)

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

SELECT load_extension('./my_extension');

See Also

• Aggregate Functions for functions that operate across groups of rows
• Expressions for operator syntax, CAST, CASE, and subqueries
• Data Types for storage classes and type affinity
• SELECT for using functions in queries