SQLite § stdlib

Built-in functions

SQLite ships with a substantial library of built-in functions: scalar functions that transform a single value, aggregate functions that summarise a column over rows, window functions that share the surface of aggregates but produce a value per row, and table-valued functions that return rows. The library is the canonical surface against which application queries are written; functions outside the library are added through the C API or through extensions.

This page is an organised reference to the built-in functions, grouped by category: numeric, string, date and time, type conversion, JSON, conditional, aggregate, and miscellaneous. The aim is to enumerate the surface so that a working programmer knows what is available without reaching for the reference.

Numeric functions

Function	Returns
`abs(x)`	The absolute value.
`ceil(x)`, `ceiling(x)`	Smallest integer ≥ x (3.35+).
`floor(x)`	Largest integer ≤ x (3.35+).
`round(x, n)`	x rounded to n decimal places (default 0).
`mod(x, y)`	The remainder of `x / y` (3.35+; same as `x % y`).
`pow(x, y)`, `power(x, y)`	x raised to y (3.35+).
`exp(x)`, `ln(x)`, `log(x)`, `log10(x)`, `log2(x)`, `log(b, x)`	Exponential and logarithm (3.35+).
`sqrt(x)`	The square root.
`sin(x)`, `cos(x)`, `tan(x)`	Trigonometric functions in radians (3.35+).
`asin(x)`, `acos(x)`, `atan(x)`, `atan2(y, x)`	Inverse trigonometric.
`sinh(x)`, `cosh(x)`, `tanh(x)`	Hyperbolic.
`asinh(x)`, `acosh(x)`, `atanh(x)`	Inverse hyperbolic.
`pi()`	The constant π.
`degrees(x)`, `radians(x)`	Convert between radians and degrees.
`random()`	A pseudo-random integer in `[-2^63, 2^63)`.
`randomblob(n)`	A pseudo-random BLOB of n bytes.
`min(x, y, …)`	Minimum (scalar form, distinct from the aggregate).
`max(x, y, …)`	Maximum (scalar form, distinct from the aggregate).
`sign(x)`	-1, 0, or 1 depending on the sign (3.35+).

The mathematical functions added in 3.35 (2021) require the build option SQLITE_ENABLE_MATH_FUNCTIONS; the standard distributions enable it. Earlier versions required the application to register the functions manually.

SELECT round(3.14159, 2);                 -- 3.14
SELECT pow(2, 10);                        -- 1024.0
SELECT pi();                              -- 3.141592653589793
SELECT abs(-5), sign(-5), sign(5), sign(0); -- 5, -1, 1, 0
SELECT random();                          -- random integer
SELECT abs(random() % 100);               -- random integer in [0, 100)

String functions

Function	Returns
`length(s)`	Character count for TEXT, byte count for BLOB.
`octet_length(s)`	Byte count regardless of type (3.43+).
`substr(s, start, len)`, `substring(s, start, len)`	Substring; 1-indexed; negative `start` from end.
`replace(s, old, new)`	Replace all occurrences.
`instr(s, t)`	1-based position of t in s; 0 if absent.
`lower(s)`	Lowercase (ASCII letters only by default).
`upper(s)`	Uppercase (ASCII letters only by default).
`ltrim(s)`, `ltrim(s, t)`	Trim leading whitespace (or characters in t).
`rtrim(s)`, `rtrim(s, t)`	Trim trailing.
`trim(s)`, `trim(s, t)`	Trim both.
`printf(fmt, …)`, `format(fmt, …)`	C-style formatting; SQLite-specific `%q`, `%Q`, `%w`.
`char(c1, c2, …)`	A string assembled from Unicode code points.
`unicode(s)`	The code point of the first character of s.
`quote(v)`	A string suitable for inclusion in an SQL literal.
`concat(s1, s2, …)`	Concatenation, treating NULL as empty string (3.44+).
`concat_ws(sep, s1, s2, …)`	Separator-joined concatenation (3.44+).
`like(pat, s, esc)`	Function form of `LIKE`.
`glob(pat, s)`	Function form of `GLOB`.
`soundex(s)`	The Soundex code of the string (compile-time option).
`hex(s)`	Hexadecimal representation of a BLOB or TEXT.
`unhex(s)`	Parse a hexadecimal string back to a BLOB (3.41+).
`zeroblob(n)`	A BLOB of n zero bytes.

The string library covers the manipulations that occur in everyday SQL. The earlier Strings and BLOBs page covers the most-used examples in detail; this page enumerates the function surface.

SELECT length('hello'), upper('hello'), substr('hello', 2, 3);
-- 5, 'HELLO', 'ell'

SELECT printf('%s is %d years old', name, age) FROM person;

SELECT concat_ws(', ', first_name, last_name) FROM person;       -- '<first>, <last>'

SELECT hex(X'cafe');                                              -- 'CAFE'
SELECT unhex('cafe');                                             -- BLOB X'cafe'

Date and time functions

The date functions accept date and time values in any of three encodings: ISO 8601 text, Julian day number (REAL), or Unix epoch (INTEGER seconds since 1970-01-01). The conventional choice for new code is ISO 8601 text, because it sorts correctly and round-trips through text export.

Function	Returns
`date(t, modifier, …)`	A date string, `'YYYY-MM-DD'`.
`time(t, modifier, …)`	A time string, `'HH:MM:SS'`.
`datetime(t, modifier, …)`	A combined date-time string, `'YYYY-MM-DD HH:MM:SS'`.
`julianday(t, modifier, …)`	The Julian day number as REAL.
`unixepoch(t, modifier, …)`	The Unix epoch in seconds (3.38+).
`strftime(fmt, t, modifier, …)`	A formatted date-time string.
`timediff(a, b)`	Difference between two dates as `'+/-YYYY-MM-DD HH:MM:SS.SSS'` (3.43+).

The first argument is a time value; subsequent arguments are modifiers that adjust it.

The principal time-value forms:

Time value	Meaning
`'now'`	The current UTC time.
`'YYYY-MM-DD'`	A date.
`'YYYY-MM-DDTHH:MM:SS'`	A combined date-time.
`'YYYY-MM-DDTHH:MM:SS.SSS'`	With sub-second precision.
`julianday-real-number`	A Julian day.
`unixepoch-integer`	A Unix epoch in seconds.
`'NNNNN.NNNN'`	A Julian day as text.

The modifiers (each is a separate string argument):

Modifier	Effect
`'+N days'`, `'-N days'`, `'+N hours'`, etc.	Add or subtract a duration.
`'start of day'`, `'start of month'`, `'start of year'`	Truncate.
`'weekday N'`	Advance to the next given weekday (0 = Sunday).
`'utc'`, `'localtime'`	Adjust to UTC or local time.

SELECT date('now');                              -- today, UTC
SELECT date('now', 'start of month');            -- first day of this month
SELECT date('now', '-1 month', 'start of month'); -- first day of last month
SELECT datetime('now', '+30 days');              -- thirty days from now
SELECT julianday('now') - julianday('1970-01-01'); -- days since Unix epoch
SELECT strftime('%Y-%m', occurred_at) AS month FROM event;

The strftime format specifiers are the conventional Unix ones: %Y (year), %m (month, 01-12), %d (day, 01-31), %H (hour, 00-23), %M (minute), %S (second), %f (fractional seconds), %j (day of year), %w (day of week), %% (literal percent), and several others; the full list is at sqlite.org/lang_datefunc.html.

Type-conversion functions

Function	Returns
`cast(x AS type)`	An expression form, not a function; converts the value.
`typeof(x)`	The storage class of the value: `'null'`, `'integer'`, `'real'`, `'text'`, `'blob'`.
`iif(cond, then, else)`	Inline conditional (3.32+); equivalent to `CASE`.
`coalesce(a, b, c, …)`	First non-NULL argument.
`ifnull(a, b)`	First non-NULL of two arguments.
`nullif(a, b)`	NULL if a equals b; otherwise a.

SELECT cast('42' AS INTEGER);             -- 42
SELECT typeof(42), typeof('x');           -- 'integer', 'text'
SELECT iif(age >= 18, 'adult', 'minor') FROM person;
SELECT coalesce(nickname, first_name, 'anonymous') FROM person;

JSON functions

The JSON library is documented in detail under JSON; the principal surface:

Function	Returns
`json(text)`, `jsonb(text)`	Parse and re-emit as JSON (text or binary).
`json_array(…)`, `json_object(…)`	Construct a JSON array or object.
`json_extract(j, path, …)`	Extract a value at a path.
`j -> path`, `j ->> path`	Operator forms of `json_extract`.
`json_set(j, path, val, …)`, `json_replace`, `json_insert`, `json_remove`, `json_patch`	Modify.
`json_each(j)`, `json_tree(j)`	Table-valued: iterate elements.
`json_valid(s)`, `json_type(j, path)`, `json_array_length(j, path)`	Inspect.
`json_group_array(col)`, `json_group_object(key, val)`	Aggregates.
`json_quote(s)`	A JSON string literal.

Aggregate functions

Function	Returns
`count(*)`	Number of rows.
`count(col)`	Number of non-NULL values.
`count(DISTINCT col)`	Number of distinct non-NULL values.
`sum(col)`	Sum; NULL on empty/all-NULL.
`total(col)`	Sum; 0.0 on empty/all-NULL.
`avg(col)`	Mean of non-NULL values.
`min(col)`, `max(col)`	Smallest/largest non-NULL value.
`group_concat(col, sep)`, `string_agg(col, sep)`	Concatenate non-NULL values.
`json_group_array(col)`, `json_group_object(k, v)`	JSON aggregates.

The aggregate library is covered in detail under Aggregation and grouping. Every aggregate may also be used as a window function with OVER (…) (see Window functions).

Window-only functions

Function	Returns
`row_number()`	1-based position within the partition.
`rank()`	Rank with gaps after ties.
`dense_rank()`	Rank without gaps.
`percent_rank()`, `cume_dist()`	Percentile rank, cumulative distribution.
`ntile(n)`	The row’s n-tile bucket.
`lag(col, n, default)`, `lead(col, n, default)`	Value from n rows away.
`first_value(col)`, `last_value(col)`, `nth_value(col, n)`	Value from a frame position.

Miscellaneous

Function	Purpose
`changes()`	Number of rows affected by the most recent statement.
`total_changes()`	Total rows affected since the connection opened.
`last_insert_rowid()`	Most-recent auto-assigned rowid.
`sqlite_version()`	The SQLite library version.
`sqlite_source_id()`	A check-in identifier of the source.
`sqlite_compileoption_used(name)`	Was the named compile option enabled?
`sqlite_offset(col)`	Byte offset of a column’s value in the file (3.31+).
`likelihood(b, p)`, `likely(b)`, `unlikely(b)`	Hints to the planner about predicate selectivity.
`error_log_callback`, `printf`	Diagnostic.

INSERT INTO event (payload) VALUES ('…');
SELECT last_insert_rowid();              -- the new id
SELECT changes();                        -- 1

SELECT sqlite_version();                 -- '3.46.0'

User-defined functions

The C API permits applications to register their own scalar, aggregate, and window functions. The relevant entry points are sqlite3_create_function_v2 (for scalar and aggregate) and sqlite3_create_window_function (for window). The bindings expose a higher-level interface:

# Python sqlite3
def title_case(s):
    return s.title() if s else None

conn.create_function('title_case', 1, title_case, deterministic=True)
conn.execute("SELECT title_case(name) FROM customer")

Registered functions appear in subsequent SQL exactly as built-in functions do. The deterministic flag (and the SQL keyword DETERMINISTIC when registering at the C level) permits the planner to use the function in expression indexes and to factor invariant calls out of loops.

Loadable extensions

Many functions and modules are not bundled with the engine but are available as loadable extensions — shared libraries that register their objects through sqlite3_load_extension. The shell command .load <library> loads an extension at runtime, provided the host application has enabled extension loading (sqlite3_enable_load_extension).

The most-used extensions are bundled in the SQLite source: regexp.c for the REGEXP operator, series.c for generate_series, csv.c for CSV-as-virtual-table, dbstat.c for the storage statistics. Third-party extensions extend the surface further: SpatiaLite for geospatial work, sqlite-utils for command-line helpers, the language-specific bindings’ helpers.

The full reference for built-in functions is at sqlite.org/lang_corefunc.html (scalar functions), sqlite.org/lang_aggfunc.html (aggregates), sqlite.org/lang_datefunc.html (date/time), and sqlite.org/lang_mathfunc.html (math). The module pages — sqlite.org/json1.html, sqlite.org/fts5.html, sqlite.org/rtree.html — document the optional surfaces.