> ## Documentation Index
> Fetch the complete documentation index at: https://docs.wirekite.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Built-in functions

> The functions available in Sumatra compute position — null handling, math, text, and date/time — with their exact semantics.

Sumatra's function catalog is deliberately small: a basic programming
language's daily-use kernel, not a SQL reference. The split to remember:

* **Inside the opaque read** (and inside server-side statements) you
  are writing the engine's SQL — *every* function the engine has is
  available there, and heavyweight transformation (regex, format
  masks, text parsing) belongs there.
* **In compute position** — expressions on variables and `rec` fields —
  the functions on this page are the supported set. They run in
  Sumatra's own evaluator, bit-exact to the engine's semantics.

Unless noted otherwise, a NULL argument makes the result NULL.

## Null handling

| Function              | Behavior                                         |
| --------------------- | ------------------------------------------------ |
| `COALESCE(a, b, ...)` | First non-NULL argument, evaluated left to right |
| `NULLIF(a, b)`        | NULL if `a = b`, otherwise `a`                   |
| `GREATEST(a, b, ...)` | Largest argument                                 |
| `LEAST(a, b, ...)`    | Smallest argument                                |

Operands of `GREATEST`, `LEAST` (and `CASE` arms) must share a type
family — mixing `TEXT` with numbers or dates is a compile error asking
for an explicit `CAST`, rather than a runtime coercion that faults only
on bad data.

## Math

| Function               | Behavior                                                                                                                         |
| ---------------------- | -------------------------------------------------------------------------------------------------------------------------------- |
| `ABS(x)`               | Absolute value, type-preserving. `ABS` of the most negative integer faults with `OVERFLOW`                                       |
| `MOD(a, b)`            | Remainder; integer and decimal operands (no float form)                                                                          |
| `FLOOR(x)` / `CEIL(x)` | On `NUMERIC`, keeps the scale (`FLOOR` of `1.55` at scale 2 is `1.00`); on integers and floats, returns `DOUBLE`                 |
| `ROUND(x [, d])`       | On `NUMERIC`: **half-up**, scale preserved. On floats: returns `DOUBLE`, rounds **half-even**. `d` must be a literal digit count |
| `TRUNC(x [, d])`       | Truncates toward zero; `d` must be a literal digit count                                                                         |
| `SQRT(x)`              | `DOUBLE` only — cast a `NUMERIC` operand first (`x::DOUBLE`); a negative argument faults                                         |
| `PI()`                 | `3.141592653589793`                                                                                                              |

Division is the `/` operator; dividing by zero faults with
`ZERO_DIVIDE` (see [numeric semantics](/sumatra/language#numeric-semantics)
for the width, promotion, and overflow rules).

## Text

| Function                                    | Behavior                                                                                                 |
| ------------------------------------------- | -------------------------------------------------------------------------------------------------------- |
| `LENGTH(s)`                                 | Character count (not bytes)                                                                              |
| `UPPER(s)` / `LOWER(s)`                     | Case conversion                                                                                          |
| `TRIM(s)` / `LTRIM(s)` / `RTRIM(s)`         | One argument: strips spaces. `LTRIM`/`RTRIM` with a second argument strip that set of characters instead |
| `SUBSTR(s, start [, len])`                  | 1-based; an out-of-range window saturates rather than faulting; a negative length faults                 |
| `POSITION(needle IN haystack)`              | 1-based position of the first match, `0` if absent                                                       |
| `REPLACE(s, from, to)`                      | Replace every occurrence                                                                                 |
| `SPLIT_PART(s, delim, n)`                   | The n-th field after splitting on `delim`                                                                |
| `LPAD(s, len, fill)` / `RPAD(s, len, fill)` | Pad to `len`                                                                                             |
| `CONCAT(a, b, ...)`                         | Variadic concatenation; integer, decimal, and date operands are coerced to text                          |

Two operator companions: `||` concatenates two strings (NULL
propagates), and `LIKE` (with the usual `%` and `_` wildcards) is the
language's pattern match.

## Date and time

| Function                             | Behavior                                                                                                       |
| ------------------------------------ | -------------------------------------------------------------------------------------------------------------- |
| `CURRENT_DATE` / `CURRENT_TIMESTAMP` | Keywords, no parentheses; UTC, from the running program's clock                                                |
| `DATE_ADD(unit, n, d)`               | Add `n` units; **returns a `TIMESTAMP`** even for a `DATE` input — cast back when storing into a `DATE` column |
| `DATE_DIFF(unit, a, b)`              | Whole units between two dates/timestamps                                                                       |
| `EXTRACT(unit, ts)`                  | One time part as a number; fractional seconds come back as a decimal                                           |

The unit is a quoted string, first argument: `'year'`, `'month'`,
`'day'`, `'hour'`, `'minute'`, `'second'`.

```sql theme={null}
rec.due_date   := CAST(DATE_ADD('day', 30, rec.invoice_date) AS DATE);
minutes_since  := DATE_DIFF('minute', last_ts, rec.event_ts);
rec.year_part  := EXTRACT('year', rec.event_ts);
```

An out-of-range result (before year 1 or after year 9999) faults with
`OVERFLOW`. Time parts cannot be extracted from a plain `DATE`.
`INTERVAL` arithmetic (`ts - INTERVAL '24' HOUR`) is SQL-side — use it
inside reads and write statements.

## Casts and conversions

`CAST(x AS T)` and the shorthand `x::T` convert between compute types:

| Conversion                | Rule                                                                                    |
| ------------------------- | --------------------------------------------------------------------------------------- |
| `DOUBLE`/`REAL` → integer | Rounds **half-even** (`2.5` → `2`, `3.5` → `4`)                                         |
| `NUMERIC` → integer       | Rounds **half-up**                                                                      |
| Integer → `NUMERIC(p,s)`  | Exact; overflow past the precision faults                                               |
| `TIMESTAMP` → `DATE`      | Truncates the time part                                                                 |
| Anything ↔ `TEXT`         | **Not available in compute position** — parse and format text in the read's SQL instead |

These are the same conversions assignment applies implicitly when the
declared type differs from the value's type.