Built-in functions

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 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'.

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.

Introduction

Getting Started

Writing Programs

Reference

Null handling

Math

Text

Date and time

Casts and conversions

​Null handling

​Math

​Text

​Date and time

​Casts and conversions

Null handling

Math

Text

Date and time

Casts and conversions