SQL Functions Reference

Returns the number of rows matching a query, optionally counting non-NULL values of an expression or distinct values. COUNT(*) counts every row including NULLs, while COUNT(expr) skips NULLs.

Adds up all non-NULL numeric values in a column or expression. Returns NULL (not 0) if every input row is NULL or the result set is empty.

Computes the arithmetic mean of non-NULL numeric values. NULL rows are excluded from both the sum and the divisor, so AVG is not equivalent to SUM/COUNT(*) when NULLs are present.

Returns the smallest non-NULL value in a group, using natural ordering for the column's data type. Works for numbers, dates, and strings (lexicographic).

Returns the largest non-NULL value in a group by the column's natural ordering. Useful for latest timestamps, top prices, or alphabetically last strings.

Concatenates non-NULL string values across a group into a single delimited string. Postgres and SQL Server spell it STRING_AGG; MySQL and SQLite use GROUP_CONCAT with a SEPARATOR clause.

Collects grouped values into a single array, preserving order when ORDER BY is supplied. This is a Postgres feature; MySQL has no direct array type but JSON_ARRAYAGG serves the same role.

Joins two or more strings end-to-end and returns a single string. CONCAT is portable across MySQL, Postgres 9.1+, and SQLite 3.44+; the standard || operator works in Postgres and SQLite but not MySQL unless PIPES_AS_CONCAT is set.

Returns the number of characters in a string (CHAR_LENGTH) or the number of bytes (LENGTH in MySQL). Postgres and SQLite LENGTH return character count for text, matching CHAR_LENGTH.

Extracts a substring by 1-based start position and optional length. All three dialects support the comma-argument form; Postgres also accepts the SQL-standard FROM/FOR clauses and POSIX regex extraction.

Replaces every occurrence of from_str inside source with to_str. Case-sensitive in Postgres and SQLite; MySQL follows the column's collation.

Converts a string to lowercase using the database's active collation/locale. Universally supported and safe for normalizing case-insensitive lookups.

Converts a string to uppercase using the current collation/locale. The mirror of LOWER, and equally universal across dialects.

Removes matching characters from the start, end, or both sides of a string. Defaults to stripping spaces when no character set is supplied.

Returns the 1-based index of the first occurrence of substr in string, or 0 if not found. POSITION is SQL-standard (Postgres/MySQL); INSTR is the MySQL/SQLite shorthand.

LEFT returns the first n characters, RIGHT returns the last n characters. Available in Postgres and MySQL. SQLite has no LEFT/RIGHT — use SUBSTR(s, 1, n) for LEFT and SUBSTR(s, -n) for RIGHT.35+; a portable fallback is SUBSTRING(str, 1, n) or SUBSTRING(str, LENGTH(str)-n+1).

Splits a string on a delimiter and returns the Nth part (1-based). Native to Postgres; MySQL emulates via SUBSTRING_INDEX and SQLite requires application-side splitting.

Returns the current transaction (or statement) timestamp. Both spellings work in Postgres and MySQL; SQLite prefers CURRENT_TIMESTAMP or datetime('now').

Rounds a timestamp down to the beginning of the specified unit (year, month, week, day, hour, etc.). Native to Postgres; MySQL uses DATE_FORMAT or manual arithmetic.

Pulls a numeric component (year, month, day, hour, minute, dow, epoch) out of a date or timestamp. EXTRACT is SQL-standard and works in Postgres and MySQL; DATE_PART is a Postgres synonym.

Adds or subtracts a duration to a date/timestamp. Postgres uses the SQL-standard INTERVAL literal directly; MySQL wraps it in DATE_ADD/DATE_SUB; SQLite uses date modifiers.

Rounds a numeric value to the specified number of decimal places (default 0). Uses banker's rounding (half-to-even) on Postgres numeric; half-away-from-zero on MySQL and float types.

Returns the smallest integer greater than or equal to the value — rounding always toward positive infinity. CEIL and CEILING are aliases in every major dialect.

Returns the largest integer less than or equal to the value — always rounds toward negative infinity. The mirror of CEIL.

Returns the first non-NULL argument, or NULL if every argument is NULL. Short-circuit evaluated — later expressions are not computed once a non-NULL value is found.

Returns NULL if value1 equals value2, otherwise returns value1. Handy for converting sentinel values (empty strings, 0) into NULL, or safely guarding against division by zero.

SQL's portable if/else-if expression. Evaluates each WHEN clause in order and returns the first matching result; returns NULL from an unmatched CASE without ELSE.

Return the largest (GREATEST) or smallest (LEAST) of the provided arguments row-by-row — not across rows, which is MIN/MAX. NULL handling differs by engine: Postgres ignores NULL args, MySQL returns NULL if any argument is NULL.

Postgres function that returns a copy of target with the value at path replaced by new_value. Creates the key if create_missing (default true); paths use text arrays like '{users,0,name}'.

Postgres aggregate that collects rows into a jsonb array. Its sibling jsonb_object_agg builds a jsonb object from key/value pairs.

Postgres JSON navigation operators. Single-arrow -> and ->> take one key or index; hash-arrows #> and #>> take a text-array path for deep access. The double-arrow forms (->>, #>>) always return text; the single-arrow forms return jsonb.

Window functions that assign an ordinal within a partition. ROW_NUMBER gives every row a distinct sequential integer; RANK and DENSE_RANK repeat values on ties (RANK leaves gaps, DENSE_RANK does not).