Scalar functions

Function names are case-insensitive. A function given a null argument answers null unless its row says otherwise. Aggregates (COUNT, SUM, MEDIAN, …) are listed under Queries, window functions under Window functions, the JSON functions on their own page, and CAST, TRY_CAST, IF, CASE and the operators under Expressions and types.

Strings

length(), substr(), upper()/lower(), the functions in this table and LIKE’s _ count characters, as Postgres and DuckDB do; strlen() counts bytes. A byte that is not UTF-8 counts as one character, so mis-encoded text never fails a load.

Searching rows

search(cols, text[, unaccent]) is true when a row holds text in the given columns: * for every column, t.* for one table of a join, * EXCEPT (a, b) or t.* EXCEPT (…) to leave some out, (a, b) or a single column to name them. The text is a small query: words must all appear, each in any of the columns; -word keeps the rows without it; col:word looks in that column only (a name that is none of them leaves the whole token as text); "two words" is one phrase. All of it ignores case — accented, Greek and Cyrillic letters too, as upper() and lower() fold them, so crédito finds CRÉDITO (an accent still counts: credito does not, unless the third argument is true, which drops accents on both sides as unaccent() does — credito then finds CRÉDITO and strasse finds Straße; it may be a bool $param) — and a value is matched as basalt prints it — a date as 2026-03-01, a decimal at its scale — so 2026-03 finds March and 10.50 that amount; a NULL holds nothing. The REPL’s \view find (f) is the same search over the rows it shows.

SELECT * FROM 'orders.csv' WHERE search(*, 'north -cancelled');
SELECT o.id, c.name FROM 'orders.csv' o JOIN 'customers.csv' c ON c.id = o.cust
WHERE search(c.*, '"são paulo"');
SELECT COUNT_IF(search((note, memo), 'urgent')) AS urgent FROM 'orders.csv';
SELECT * FROM 'payments.csv' WHERE search(kind, 'credito', true);

It reads whole rows, so it is never pushed to a source: a SQL read sends every column and filters here, and a WHERE with search stays after the selects and joins it follows. In a join’s ON it applies to the joined pair — filtering an inner join, deciding the matches of an outer one. The columns are resolved when the query is planned, so an unknown name fails check; the text may be a literal or a $param. Every searched value is turned into text for each row, so naming the columns (search((a, b), …)) is faster than * on a wide table.

Regular expressions

The pattern syntax is on Regular expressions. A literal pattern is compiled at plan time, so a malformed one fails check.

Hashes and encodings

The hashes are over the value’s text. For a row’s change key, write md5(concat_ws('|', a, b, c)): concat would make it null whenever a column is.

Numbers

Nulls and comparison

Dates and times

The units are year, month, week, day, hour, minute and second. week is the ISO week: it starts on Monday, extract numbers it 1–53 with week 1 holding the year’s first Thursday, and date_diff counts the Mondays crossed. check rejects an unknown unit even over a SQL table whose columns it has not seen.

The format codes are %Y %m %d %H %M %S %y %%. strptime takes a number shorter than its width (3/1/2026) and pivots %y at 69. Text that does not fit the format, or a day that does not exist, is an error that stops the statement, as a failed CAST does — one bad date in a file loads nothing; try_strptime makes it null instead and the load goes on.

Bits

The bitwise operators & | ^ << >> ~ are under Expressions and types.