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)

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

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

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)

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.

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

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.

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 octet_length('Hello');    -- 5 (ASCII, 1 byte per char)

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)

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.

-- Whitespace trimming
SELECT ltrim('   Hello');          -- 'Hello'
SELECT rtrim('Hello   ');          -- 'Hello'
SELECT trim('   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)

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

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'

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