Reference · 89 entries
SQLite Reference
The SQLite 3 function reference: string, math, date, JSON, aggregate and window functions with syntax, the SQLite version that added each, and examples you can run in your browser.
SQLite 3 Version history
Examples run in your browser on a fresh copy of the sample shop database
(customers, products, orders, order_items, employees), so changes never stick. Each page shows the SQLite version
that added a function, linked to the SQL version history. The in-browser SQLite (3.49)
has no math functions such as ceil() or log(), so those pages show results from standard SQLite instead.
String Functions
SQLite string functions and operators: upper(), substr(), instr(), replace(), trim(), concat(), format(), the || operator, LIKE and GLOB, with runnable examples.
| Name | Description |
|---|---|
UPPER() | Return a copy of a string with ASCII letters converted to uppercase. Letters such as é are left unchanged. |
LOWER() | Return a copy of a string with ASCII letters converted to lowercase. Handy for case-insensitive comparisons. |
LENGTH() | Return the number of characters in a string, or the number of bytes in a BLOB. NULL gives NULL. |
OCTET_LENGTH() | Return the number of bytes a string takes in the database encoding (usually UTF-8), not the number of characters. |
SUBSTR() | Return part of a string starting at a 1-based position, with an optional length. Negative starts count from the end. |
INSTR() | Return the 1-based position of the first occurrence of one string inside another, or 0 if it isn't found. |
REPLACE() | Replace every occurrence of one string with another inside a string. The search is case-sensitive. |
TRIM() | Remove spaces, or any characters you list, from both ends of a string. Spaces inside the string are kept. |
LTRIM() | Remove spaces, or characters you list, from the start (left side) of a string only. |
RTRIM() | Remove spaces, or characters you list, from the end (right side) of a string only. |
|| | Join strings with the || operator, the standard SQL way to concatenate. If either side is NULL, the result is NULL. |
CONCAT() | Join any number of values into one string. Unlike ||, NULL arguments are skipped instead of making the result NULL. |
CONCAT_WS() | Join values with a separator between them, skipping NULLs. The ws stands for with separator. |
FORMAT() | Build a string from a template with printf-style placeholders such as %s, %d, %.2f and %,d. printf() is the old name. |
CHAR() | Return a string made from Unicode code point numbers, such as char(72, 105) for 'Hi'. The reverse of unicode(). |
UNICODE() | Return the Unicode code point of the first character of a string, such as 65 for 'A'. |
HEX() | Return the hexadecimal form of a value's bytes (its UTF-8 text for strings). Often used to display BLOBs. |
UNHEX() | Decode a hexadecimal string into a BLOB, the reverse of hex(). Returns NULL if the text isn't valid hex. |
QUOTE() | Return a value written as an SQL literal: text in single quotes with quotes doubled, BLOBs as X'..', NULL as NULL. |
LIKE | Match a string against a pattern with % (any run of characters) and _ (one character). Case-insensitive for ASCII. |
GLOB | Match a string against a Unix-style pattern with *, ? and [...]. Unlike LIKE, GLOB is case-sensitive. |
Math Functions
SQLite numeric functions: abs(), round(), random(), sign(), plus the built-in math functions ceil(), floor(), trunc(), mod(), power(), sqrt() and log().
| Name | Description |
|---|---|
ABS() | Return the absolute value of a number, so abs(-7) is 7. NULL gives NULL and text that isn't a number gives 0.0. |
ROUND() | Round a number to a given number of decimal places (0 by default). Halves round away from zero. |
SIGN() | Return -1, 0 or 1 depending on whether a number is negative, zero or positive. Non-numeric text gives NULL. |
RANDOM() | Return a random 64-bit signed integer. Use abs(random()) % n or ORDER BY random() for random choices. |
CEIL() | Return the smallest integer value not less than a number, rounding up: ceil(2.1) is 3. ceiling() is a synonym. |
FLOOR() | Return the largest integer value not greater than a number, rounding down: floor(2.9) is 2 and floor(-2.1) is -3. |
TRUNC() | Drop the fractional part of a number, rounding toward zero: trunc(2.7) is 2 and trunc(-2.7) is -2. |
MOD() | Return the remainder of dividing one number by another. Unlike the % operator it also works with decimals. |
POWER() | Raise a number to a power, so power(2, 10) is 1024. pow() is another name for the same function. |
SQRT() | Return the square root of a number, such as sqrt(16) = 4. Negative numbers give NULL rather than an error. |
EXP() | Return e (about 2.718) raised to a power, so exp(1) is e. It is the inverse of the ln() function. |
LN() | Return the natural logarithm (base e) of a number. Zero or negative numbers give NULL. |
LOG() | Return the base-10 logarithm of a number, or the logarithm in any base with log(B, X). log10() and log2() also exist. |
PI() | Return an approximation of pi (3.14159...). Useful for circles and converting between degrees and radians. |
Conditional and Type Functions
Handle NULLs and conditions in SQLite with coalesce(), ifnull(), nullif(), iif() and CASE, and check or convert types with typeof() and CAST.
| Name | Description |
|---|---|
COALESCE() | Return the first argument that isn't NULL. The standard way to replace NULL with a default value. |
IFNULL() | Return the first argument if it isn't NULL, otherwise the second. A two-argument coalesce(). |
NULLIF() | Return NULL if two values are equal, otherwise the first value. Often used to avoid division by zero. |
IIF() | Return one value if a condition is true and another if it's false: a short form of CASE WHEN … THEN … ELSE … END. |
CASE | Choose a value from conditions: CASE WHEN cond THEN result … ELSE default END. Works in SELECT, WHERE and ORDER BY. |
TYPEOF() | Return the storage class of a value: 'null', 'integer', 'real', 'text' or 'blob'. Shows how SQLite stored it. |
CAST | Convert a value to another type: CAST(X AS INTEGER), REAL, TEXT, NUMERIC or BLOB. Text that isn't a number becomes 0. |
LAST_INSERT_ROWID() | Return the rowid of the most recent successful INSERT on this connection, such as a new auto-generated ID. |
CHANGES() | Return how many rows the most recent INSERT, UPDATE or DELETE changed. total_changes() counts since the connection opened. |
Aggregate Functions
SQLite aggregate functions: count(), sum(), total(), avg(), min(), max(), group_concat() and string_agg(), with GROUP BY, FILTER and ORDER BY examples.
| Name | Description |
|---|---|
COUNT() | Count rows: count(*) counts every row, count(X) counts rows where X isn't NULL, count(DISTINCT X) counts unique values. |
SUM() | Add up the non-NULL values in a group. Returns NULL when there are no values; total() returns 0.0 instead. |
TOTAL() | Add up the non-NULL values in a group like sum(), but always return a real number and 0.0 for no rows. |
AVG() | Return the average of the non-NULL values in a group, always as a real number. NULLs are ignored, not counted as 0. |
MIN() | With one argument, return the smallest value in a group. With two or more, return the smallest of the arguments. |
MAX() | With one argument, return the largest value in a group. With two or more, return the largest of the arguments. |
GROUP_CONCAT() | Join a group's non-NULL values into one string, separated by commas or a separator you give. |
STRING_AGG() | Join a group's values with a separator, the standard name also used by PostgreSQL and SQL Server. |
Date and Time Functions
SQLite date functions date(), time(), datetime(), julianday(), unixepoch(), strftime() and timediff(), with modifiers such as '+1 month' and 'start of month'.
| Name | Description |
|---|---|
DATE() | Return a date as text in YYYY-MM-DD form, optionally shifted with modifiers like '+7 days' or 'start of month'. |
TIME() | Return the time part of a date/time as text in HH:MM:SS form, optionally shifted with modifiers. |
DATETIME() | Return a date and time as text in YYYY-MM-DD HH:MM:SS form, optionally shifted with modifiers. |
JULIANDAY() | Return the Julian day number (days since 4714 BC, with a fraction for the time). Subtract two to get days between dates. |
UNIXEPOCH() | Return a date/time as a Unix timestamp: whole seconds since 1970-01-01 00:00:00 UTC. |
STRFTIME() | Format a date/time with % codes, such as %Y year, %m month, %d day, %H hour, %W week and %w weekday. |
TIMEDIFF() | Return the difference between two dates as text like '+0000-01-14 00:00:00.000' (years, months, days, time). |
JSON Functions
SQLite JSON functions and operators: json_extract(), ->, ->>, json_object(), json_set(), json_each(), json_group_array() and more, with runnable examples.
| Name | Description |
|---|---|
JSON() | Check and minify JSON text: return it without extra whitespace, or raise an error if it isn't valid JSON. |
JSON_EXTRACT() | Read one or more values from JSON by path. JSON objects and arrays come back as text; numbers and strings as SQL values. |
-> and ->> | Extract from JSON with -> (returns JSON text) or ->> (returns an SQL value). Paths can be '$.key' or just a key or index. |
JSON_OBJECT() | Build a JSON object from key and value pairs. SQL NULL becomes JSON null; nested json() values stay JSON. |
JSON_ARRAY() | Build a JSON array from the arguments. SQL NULL becomes JSON null; numbers stay numbers. |
JSON_ARRAY_LENGTH() | Return the number of elements in a JSON array, optionally at a path. Non-arrays give 0. |
JSON_TYPE() | Return the JSON type of a value or path: 'null', 'true', 'false', 'integer', 'real', 'text', 'array' or 'object'. |
JSON_VALID() | Return 1 if a string is well-formed JSON and 0 if it isn't. Use it in CHECK constraints or before parsing. |
JSON_SET() | Return JSON with values set at paths, creating or overwriting them. json_insert() and json_replace() are stricter. |
JSON_INSERT() | Return JSON with values added at paths that don't exist yet. Existing values are left unchanged. |
JSON_REPLACE() | Return JSON with values replaced at paths that already exist. Missing paths are not created. |
JSON_REMOVE() | Return JSON with the elements at the given paths removed. Paths that don't exist are ignored. |
JSON_PATCH() | Merge one JSON object into another using RFC 7396 MergePatch: keys are added or replaced, null removes a key. |
JSON_GROUP_ARRAY() | Aggregate the values of a group into one JSON array, one element per row. SQL NULLs become JSON null. |
JSON_GROUP_OBJECT() | Aggregate key/value pairs from the rows of a group into one JSON object, such as a name-to-price lookup. |
JSON_EACH() | A table-valued function that returns one row per element of a JSON array or object, with key, value and type columns. |
JSON_TREE() | A table-valued function like json_each() that walks a JSON value recursively, returning a row for every nested element. |
JSONB() | Convert JSON text to SQLite's binary JSONB format, which is faster to process. Every json_ function has a jsonb_ version. |
JSON_PRETTY() | Return JSON formatted with new lines and indentation (4 spaces by default) so it's easy to read. |
Window Functions
SQLite window functions: row_number(), rank(), dense_rank(), ntile(), lag(), lead(), first_value() and more, with OVER, PARTITION BY and frame examples.
| Name | Description |
|---|---|
ROW_NUMBER() | Number the rows of a window 1, 2, 3, … in the order given by the window's ORDER BY. Ties get different numbers. |
RANK() | Rank rows by the window's ORDER BY, giving ties the same rank and leaving gaps after them (1, 2, 2, 4). |
DENSE_RANK() | Rank rows by the window's ORDER BY, giving ties the same rank without gaps after them (1, 2, 2, 3). |
PERCENT_RANK() | Return a row's relative rank from 0 to 1: (rank - 1) / (rows in partition - 1). |
CUME_DIST() | Return the cumulative distribution: the share of rows ordered at or before the current row, from just above 0 to 1. |
NTILE() | Split the rows of a window into N groups of nearly equal size and return each row's group number. |
LAG() | Return a value from a previous row in the window, such as last month's total, or a default when there isn't one. |
LEAD() | Return a value from a following row in the window, such as the next order's date, or a default when there isn't one. |
FIRST_VALUE() | Return a value from the first row of the window frame, such as each department's highest salary. |
LAST_VALUE() | Return a value from the last row of the window frame. Widen the frame to see the real last row of the partition. |
NTH_VALUE() | Return a value from the Nth row of the window frame, or NULL if the frame has fewer rows. |