Functions

Every function smaltdb accepts, in alphabetical order. Names are case-insensitive. The functions follow SQLite's behaviour; each page says where they differ.

Page	Contents
Scalar functions	NULL handling, conditionals, `typeof`, scalar `min`/`max`, blobs, planner hints, last-statement information
String functions	Length, case, substrings, trimming, concatenation, characters, `hex`, `quote`, `printf`
Math functions	`abs`, `round`, `sign`, `random`; `sqrt`, `pow`, `exp`, `ln`, `log`, `ceil`, `floor`, `mod`, trigonometric and hyperbolic functions
Date and time functions	`date`, `time`, `datetime`, `julianday`, `unixepoch`, `strftime`, `timediff`, `CURRENT_*`; time values, modifiers and format codes
Aggregate functions	`count`, `sum`, `total`, `avg`, `min`, `max`, `group_concat`, `string_agg`; statistics: `stddev`, `variance`, `corr`, `median`, `percentile`, …; `DISTINCT` and `FILTER`
Window functions	Ranking and navigation functions, aggregates over windows, `OVER` syntax
Table-valued functions	`read_csv`, `read_json`, `read_parquet` (from plugins), `pragma_*`

Operators (||, LIKE, GLOB, arithmetic, comparisons), CASE and CAST are not functions; see Expressions.

SELECT * FROM information_schema.routines lists the scalar and aggregate functions (not the window functions, table-valued functions or CURRENT_* keywords); see information_schema.

All functions

Function	Kind	Description
`abs(X)`	scalar	Absolute value
`acos(X)`	scalar	Arc cosine, in radians
`acosh(X)`	scalar	Inverse hyperbolic cosine
`asin(X)`	scalar	Arc sine, in radians
`asinh(X)`	scalar	Inverse hyperbolic sine
`atan(X)`	scalar	Arc tangent, in radians
`atan2(Y, X)`	scalar	Angle of the point (`X`, `Y`), in radians
`atanh(X)`	scalar	Inverse hyperbolic tangent
`avg(X)`	aggregate	Average of non-NULL values
`ceil(X)`, `ceiling(X)`	scalar	Round up
`changes()`	scalar	Rows changed by the last INSERT, UPDATE or DELETE
`char(X, …)`	scalar	String from Unicode code points
`coalesce(X, Y, …)`	scalar	First non-NULL argument
`concat(X, …)`	scalar	Concatenation, skipping NULLs
`concat_ws(SEP, X, …)`	scalar	Concatenation with a separator, skipping NULLs
`corr(Y, X)`	aggregate	Correlation coefficient
`cos(X)`	scalar	Cosine
`cosh(X)`	scalar	Hyperbolic cosine
`count(*)`, `count(X)`	aggregate	Number of rows, or of non-NULL values
`covar_pop(Y, X)`	aggregate	Population covariance
`covar_samp(Y, X)`	aggregate	Sample covariance
`cume_dist()`	window	Cumulative distribution within the partition
`CURRENT_DATE`	keyword	Current UTC date, `YYYY-MM-DD`
`CURRENT_TIME`	keyword	Current UTC time, `HH:MM:SS`
`CURRENT_TIMESTAMP`	keyword	Current UTC date and time, `YYYY-MM-DD HH:MM:SS`
`date(TIME, MOD, …)`	scalar	Date as `YYYY-MM-DD`
`datetime(TIME, MOD, …)`	scalar	Date and time as `YYYY-MM-DD HH:MM:SS`
`degrees(X)`	scalar	Radians to degrees
`dense_rank()`	window	Rank without gaps
`exp(X)`	scalar	e to the power `X`
`first_value(X)`	window	Value from the first row of the frame
`floor(X)`	scalar	Round down
`format(FORMAT, …)`	scalar	Same as `printf`
`group_concat(X [, SEP])`	aggregate	Values joined into text
`hex(X)`	scalar	Upper-case hexadecimal of the bytes
`ifnull(X, Y)`	scalar	`X` if not NULL, else `Y`
`if(COND, X, …)`	scalar	Same as `iif`
`iif(COND, X, Y)`	scalar	`X` if `COND` is true, else `Y`
`instr(X, Y)`	scalar	Position of `Y` in `X`
`julianday(TIME, MOD, …)`	scalar	Julian day number
`kurtosis(X)`	aggregate	Sample excess kurtosis
`kurtosis_pop(X)`	aggregate	Population excess kurtosis
`lag(X [, N [, DEFAULT]])`	window	Value from an earlier row
`last_insert_rowid()`	scalar	Rowid of the last inserted row
`last_value(X)`	window	Value from the last row of the frame
`lead(X [, N [, DEFAULT]])`	window	Value from a later row
`length(X)`	scalar	Characters in text, bytes in a blob
`likelihood(X, P)`	scalar	Returns `X` (planner hint, ignored)
`likely(X)`	scalar	Returns `X` (planner hint, ignored)
`ln(X)`	scalar	Natural logarithm
`log(X)`, `log(B, X)`	scalar	Base-10 logarithm, or to base `B`
`log10(X)`	scalar	Base-10 logarithm
`log2(X)`	scalar	Base-2 logarithm
`lower(X)`	scalar	ASCII lower case
`ltrim(X [, CHARS])`	scalar	Remove characters from the start
`max(X)`	aggregate	Largest value
`max(X, Y, …)`	scalar	Largest argument
`median(X)`	aggregate	Median
`min(X)`	aggregate	Smallest value
`min(X, Y, …)`	scalar	Smallest argument
`mod(X, Y)`	scalar	Remainder of `X / Y`, as a REAL
`nth_value(X, N)`	window	Value from the N-th row of the frame
`ntile(N)`	window	Bucket number 1..N
`nullif(X, Y)`	scalar	NULL if `X = Y`, else `X`
`octet_length(X)`	scalar	Bytes in the UTF-8 encoding
`percent_rank()`	window	Relative rank, 0 to 1
`percentile(X, P)`	aggregate	`P`th percentile (0–100), interpolated
`percentile_cont(X, F)`	aggregate	Percentile at fraction `F`, interpolated
`percentile_disc(X, F)`	aggregate	Percentile at fraction `F`, one of the values
`pi()`	scalar	π
`pow(X, Y)`, `power(X, Y)`	scalar	`X` to the power `Y`
`pragma_foreign_key_list(TABLE)`	table-valued	Foreign keys of a table
`pragma_index_info(INDEX)`	table-valued	Key columns of an index
`pragma_index_list(TABLE)`	table-valued	Indexes of a table
`pragma_index_xinfo(INDEX)`	table-valued	All columns of an index
`pragma_table_info(TABLE)`	table-valued	Columns of a table
`pragma_table_xinfo(TABLE)`	table-valued	`pragma_table_info` plus `hidden`
`printf(FORMAT, …)`	scalar	C-style formatted text
`quote(X)`	scalar	Value as an SQL literal
`radians(X)`	scalar	Degrees to radians
`random()`	scalar	Random 64-bit integer
`randomblob(N)`	scalar	`N` random bytes
`rank()`	window	Rank with gaps
`read_csv(LOCATION, …)`	table-valued	Rows of a CSV or TSV file (`@smaltdb/io` plugin)
`read_json(LOCATION, …)`	table-valued	Rows of a JSON or NDJSON file (`@smaltdb/io` plugin)
`read_parquet(LOCATION, …)`	table-valued	Rows of a Parquet file (`@smaltdb/parquet` plugin)
`replace(X, Y, Z)`	scalar	Replace every `Y` in `X` with `Z`
`round(X [, DIGITS])`	scalar	Round to `DIGITS` decimal places
`row_number()`	window	Row number within the partition
`rtrim(X [, CHARS])`	scalar	Remove characters from the end
`sign(X)`	scalar	-1, 0 or 1
`sin(X)`	scalar	Sine
`sinh(X)`	scalar	Hyperbolic sine
`skewness(X)`	aggregate	Sample skewness
`skewness_pop(X)`	aggregate	Population skewness
`smaltdb_version()`	scalar	The smaltdb package version
`sqrt(X)`	scalar	Square root
`stddev(X)`, `stddev_samp(X)`	aggregate	Sample standard deviation
`stddev_pop(X)`	aggregate	Population standard deviation
`strftime(FORMAT, TIME, MOD, …)`	scalar	Formatted date and time
`string_agg(X, SEP)`	aggregate	Same as `group_concat(X, SEP)`
`substr(X, START [, LENGTH])`	scalar	Part of a string or blob
`substring(X, START [, LENGTH])`	scalar	Same as `substr`
`sum(X)`	aggregate	Sum; NULL when there are no values
`tan(X)`	scalar	Tangent
`tanh(X)`	scalar	Hyperbolic tangent
`time(TIME, MOD, …)`	scalar	Time as `HH:MM:SS`
`timediff(A, B)`	scalar	Difference between two times
`total(X)`	aggregate	Sum; 0 when there are no values
`total_changes()`	scalar	Rows changed since the connection opened
`trim(X [, CHARS])`	scalar	Remove characters from both ends
`trunc(X)`	scalar	Round toward zero
`typeof(X)`	scalar	Storage class: `null`, `integer`, `real`, `text`, `blob`
`unicode(X)`	scalar	Code point of the first character
`unixepoch(TIME, MOD, …)`	scalar	Seconds since 1970-01-01
`unlikely(X)`	scalar	Returns `X` (planner hint, ignored)
`upper(X)`	scalar	ASCII upper case
`var_pop(X)`	aggregate	Population variance
`var_samp(X)`, `variance(X)`	aggregate	Sample variance
`zeroblob(N)`	scalar	`N` zero bytes

Every aggregate function can also be used as a window function with OVER; see Window functions.

Not available

These SQLite functions are not implemented. Each fails with SMALT_ERROR no such function: name (or no such table-valued function in FROM) when the statement is prepared.

Group	Functions
JSON	`json`, `jsonb`, `json_array`, `json_array_length`, `json_extract`, `json_insert`, `json_object`, `json_patch`, `json_quote`, `json_remove`, `json_replace`, `json_set`, `json_type`, `json_valid`, `json_group_array`, `json_group_object`, and the table-valued `json_each` and `json_tree`. The `->` and `->>` operators fail with `SMALT_UNSUPPORTED`.
Text	`unhex`, `unistr`, `unistr_quote`, and the function forms `like(…)` and `glob(…)` (the `LIKE` and `GLOB` operators work). `REGEXP` has no implementation.
SQLite-specific	`sqlite_version`, `sqlite_source_id`, `sqlite_compileoption_get`, `sqlite_compileoption_used`, `sqlite_offset`, `load_extension`, `subtype`
Table-valued	`generate_series` (use a recursive CTE; see SELECT)

sql

SELECT json_extract('{"a": 1}', '$.a');

sql

SELECT '{"a": 1}' ->> '$.a';

There are no user-defined functions: functions cannot be added from JavaScript.

A sequence of numbers without generate_series:

sql

WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5)
SELECT group_concat(i) AS numbers FROM n;

numbers
1,2,3,4,5

Functions ​

All functions ​

Not available ​

Functions

All functions

Not available