Built-In Scalar, Aggregate, and Core Utility Functions

Learning outcomes

Functions turn values into other values, but they are not all interchangeable. Some are scalar, some aggregate across rows, some are deterministic enough for schema expressions, and some are SQLite-specific additions that older embedded runtimes may not have.

NULL-handling functions encode fallback rules

coalesce() returns the first non-NULL argument. ifnull(X,Y) is its two-argument form. nullif(X,Y) does the inverse style of job: it returns NULL when the two arguments compare equal, which is useful for converting sentinel values into missing data.

SELECT  coalesce(NULL, NULL, 'unknown') AS first_present,  ifnull(NULL, 'fallback')        AS two_arg_fallback,  nullif('', '')                  AS blank_becomes_null,  nullif('ok', '')                AS nonblank_survives;

These functions express domain decisions. If empty string and NULL mean different things in your application, do not normalize one into the other simply because nullif() makes it convenient.

Text and numeric utilities: know exactly what each returns

SELECT  length('café') AS code_points,  substr('PUMP-007', 1, 4) AS family,  instr('PUMP-007','-') AS dash_position,  replace(' fan-014 ', 'fan', 'FAN') AS replaced,  trim('  ready  ') AS trimmed,  round(12.345, 2) AS rounded,  abs(-17) AS magnitude;

iif(), if(), and CASE: current syntax has a version history

Three-argument iif(condition, true_value, false_value) is shorthand for CASE and uses short-circuit evaluation. Current SQLite also accepts if() as an alternative spelling and allows more than three arguments, but those capabilities are recent.

SELECT iif(temperature_c >= 80, 'hot', 'normal')FROM (SELECT 86 AS temperature_c);-- Current SQLite 3.53.4 examples:SELECT if(1, 'yes', 'no');SELECT iif(0,'A', 1,'B', 'fallback');

If your application embeds an older SQLite library, prefer CASE or feature-test the runtime. The command-line shell version on a developer laptop does not prove the SQLite library inside Python, Node, a mobile app, or a browser runtime supports the same function set.

typeof(), quote(), format()/printf(), hex(), and unhex()

Utility functions are especially useful at boundaries: inspecting runtime types, producing diagnostic strings, and converting binary data to or from hexadecimal text.

SELECT  typeof(X'414243') AS blob_type,  quote(X'414243')  AS blob_literal,  hex(X'414243')    AS hex_text,  hex(unhex('414243')) AS roundtrip,  format('%s:%04d', 'device', 7) AS label,  printf('%.2f', 12.345) AS rendered_number;

unhex() is built in from SQLite 3.41.0 onward. format() and printf() are formatting tools; their text results should not be mistaken for typed numeric values. Keep raw numeric values for computation and format at a reporting boundary when possible.

min() and max(): scalar or aggregate depending on arguments

SQLite's multi-argument min(X,Y,...) and max(X,Y,...) are scalar functions: they compare arguments within one row/expression. With a single argument in an aggregate query, min(column) and max(column) summarize rows.

SELECT min(9,4,12) AS scalar_min,       max(9,4,12) AS scalar_max;WITH readings(v) AS (VALUES (9),(4),(12))SELECT min(v) AS aggregate_min,       max(v) AS aggregate_maxFROM readings;

Be cautious with NULL and collation when using multi-argument forms. A function name alone does not tell you whether it is row-preserving or row-collapsing; inspect its arguments and query context.

Aggregate FILTER keeps conditional summaries readable

SQLite aggregate invocations can include FILTER (WHERE ...). The filter decides which input rows contribute to that aggregate without forcing repeated CASE expressions.

DROP TABLE IF EXISTS function_note;CREATE TABLE function_note(  note_id INTEGER PRIMARY KEY,  device_code TEXT NOT NULL,  severity INTEGER,  status TEXT,  body TEXT);INSERT INTO function_note(device_code,severity,status,body) VALUES('PUMP-007',5,'open',' Seal leak '),('PUMP-007',2,'closed',' inspected '),('FAN-014',NULL,'open',' vibration '),('FAN-014',4,'open',' Bearing HOT ');SELECT device_code,       count(*) AS notes,       count(*) FILTER (WHERE status='open') AS open_notes,       round(avg(severity),1) AS avg_known_severity,       max(severity) AS max_severityFROM function_noteGROUP BY device_codeORDER BY device_code;

avg() ignores NULL input values. That can be correct for “average known severity,” but not necessarily for “average over all notes.” Name metrics so consumers understand the missing-data policy.

Deterministic versus non-deterministic functions

A deterministic function returns the same result for the same inputs. lower('ABC') is deterministic. random() and current-time expressions are not stable in that sense. SQLite restricts non-deterministic functions in schema contexts whose stored/indexed result must remain valid, such as expression indexes, partial-index predicates, CHECK constraints, and generated columns.

-- Suitable deterministic expression:CREATE INDEX IF NOT EXISTS idx_note_trimmed_bodyON function_note(trim(body));-- Do not design schema indexes around random() or time-varying results.SELECT random(), datetime('now');

Applications can register their own SQL functions and mark them deterministic only when that promise is actually true. Mislabeling a changing function can make index contents disagree with future function results.

Cleaning/report lab: show intermediate meaning

SELECT  note_id,  upper(device_code) AS normalized_code,  nullif(trim(body),'') AS cleaned_body,  coalesce(severity, 0) AS severity_for_display,  iif(severity IS NULL, 'missing',      iif(severity >= 4, 'high', 'normal')) AS severity_band,  format('#%d %s', note_id, trim(body)) AS report_labelFROM function_noteORDER BY note_id;

Walk left to right: identifier is unchanged; code is normalized for presentation; blank body becomes NULL; missing severity is mapped to 0 only for display; a nested three-argument iif() classifies values; format() produces final text. The query does not mutate source data.

Summary and bridge

Core functions are most useful when they make a data rule explicit: fallback, cleanup, classification, formatting, binary encoding, or aggregation. The next lesson applies the same discipline to time, where SQLite accepts several representations and modifiers but deliberately does not provide a full timezone database.

Authoritative references

• SQLite — Built-In Scalar SQL Functions.
• SQLite — Built-In Aggregate Functions.
• SQLite — Deterministic SQL Functions.
• SQLite — Release History.