MonaDB

Functions

Builtin scalar functions — the standard library. Each case evaluates a function via a bare select <expr> (no from), which yields the result value directly as a single row. Covers happy paths, dynamic-on-value dispatch, null propagation, the null-aware exceptions, and the static/runtime errors. Note: MonaDB has no unary minus, so negatives are written 0 - n.

Typeof Reports

Typeof reports the value's runtime type name.

SQL

select typeof(1);

Result

[
  "int"
]

SQL

select typeof(1);

select typeof(1.5);

Result

[
  "float"
]

SQL

select typeof(1);

select typeof(1.5);

select typeof('x');

Result

[
  "string"
]

SQL

select typeof(1);

select typeof(1.5);

select typeof('x');

select typeof(true);

Result

[
  "bool"
]

SQL

select typeof(1);

select typeof(1.5);

select typeof('x');

select typeof(true);

select typeof(null);

Result

[
  "null"
]

SQL

select typeof(1);

select typeof(1.5);

select typeof('x');

select typeof(true);

select typeof(null);

select typeof([1, 2]);

Result

[
  "array"
]

SQL

select typeof(1);

select typeof(1.5);

select typeof('x');

select typeof(true);

select typeof(null);

select typeof([1, 2]);

select typeof({a: 1});

Result

[
  "object"
]

Coalesce Returns

Coalesce returns the first non-null argument.

SQL

select coalesce(null, null, 7);

Result

[
  7
]

SQL

select coalesce(null, null, 7);

select coalesce(null, 'x');

Result

[
  "x"
]

SQL

select coalesce(null, null, 7);

select coalesce(null, 'x');

select coalesce(1, 2);

Result

[
  1
]

SQL

select coalesce(null, null, 7);

select coalesce(null, 'x');

select coalesce(1, 2);

select coalesce(null, null);

Result

[
  null
]

Nullif Yields

Nullif yields null when the two arguments are equal.

SQL

select nullif(5, 5);

Result

[
  null
]

SQL

select nullif(5, 5);

select nullif(5, 3);

Result

[
  5
]

Ifnull /

Ifnull / nvl substitute a default for null.

SQL

select ifnull(null, 'd');

Result

[
  "d"
]

SQL

select ifnull(null, 'd');

select ifnull('v', 'd');

Result

[
  "v"
]

SQL

select ifnull(null, 'd');

select ifnull('v', 'd');

select nvl(null, 9);

Result

[
  9
]

Iif Selects

Iif selects a branch on the truthiness of the condition.

SQL

select iif(true, 'y', 'n');

Result

[
  "y"
]

SQL

select iif(true, 'y', 'n');

select iif(1 = 2, 'y', 'n');

Result

[
  "n"
]

SQL

select iif(true, 'y', 'n');

select iif(1 = 2, 'y', 'n');

select iif(null, 'y', 'n');

Result

[
  "n"
]

Abs Of

Abs of ints and floats.

SQL

select abs(0 - 5);

Result

[
  5
]

SQL

select abs(0 - 5);

select abs(5);

Result

[
  5
]

SQL

select abs(0 - 5);

select abs(5);

select abs(0.0 - 2.5);

Result

[
  2.5
]

Ceil, Floor,

Ceil, floor, and trunc round floats; ints pass through.

SQL

select ceil(3.2);

Result

[
  4
]

SQL

select ceil(3.2);

select ceiling(3.2);

Result

[
  4
]

SQL

select ceil(3.2);

select ceiling(3.2);

select floor(3.8);

Result

[
  3
]

SQL

select ceil(3.2);

select ceiling(3.2);

select floor(3.8);

select trunc(3.9);

Result

[
  3
]

SQL

select ceil(3.2);

select ceiling(3.2);

select floor(3.8);

select trunc(3.9);

select ceil(7);

Result

[
  7
]

Round To

Round to nearest integer, and to n decimals.

SQL

select round(3.7);

Result

[
  4
]

SQL

select round(3.7);

select round(3.14159, 0);

Result

[
  3
]

SQL

select round(3.7);

select round(3.14159, 0);

select round(5);

Result

[
  5
]

Sign Returns

Sign returns -1, 0, or 1.

SQL

select sign(0 - 5);

Result

[
  -1
]

SQL

select sign(0 - 5);

select sign(5);

Result

[
  1
]

SQL

select sign(0 - 5);

select sign(5);

select sign(0);

Result

[
  0
]

Sqrt And

Sqrt and pow (integer powers stay integers).

SQL

select sqrt(9);

Result

[
  3
]

SQL

select sqrt(9);

select pow(2, 10);

Result

[
  1024
]

SQL

select sqrt(9);

select pow(2, 10);

select power(3, 2);

Result

[
  9
]

Exp, Ln,

Exp, ln, and log10 at exact points.

SQL

select exp(0);

Result

[
  1
]

SQL

select exp(0);

select ln(1);

Result

[
  0
]

SQL

select exp(0);

select ln(1);

select log10(1);

Result

[
  0
]

Mod Is

Mod is the integer remainder.

SQL

select mod(7, 3);

Result

[
  1
]

SQL

select mod(7, 3);

select mod(10, 5);

Result

[
  0
]

Greatest And

Greatest and least over numbers and strings.

SQL

select greatest(1, 5, 3);

Result

[
  5
]

SQL

select greatest(1, 5, 3);

select least(1, 5, 3);

Result

[
  1
]

SQL

select greatest(1, 5, 3);

select least(1, 5, 3);

select greatest('a', 'c', 'b');

Result

[
  "c"
]

Length Dispatches

Length dispatches on value — chars, elements, or members.

SQL

select length('hello');

Result

[
  5
]

SQL

select length('hello');

select length([1, 2, 3]);

Result

[
  3
]

SQL

select length('hello');

select length([1, 2, 3]);

select length({x: 1, y: 2});

Result

[
  2
]

SQL

select length('hello');

select length([1, 2, 3]);

select length({x: 1, y: 2});

select length('café');

Result

[
  4
]

Upper And

Upper and lower case strings.

SQL

select upper('hi');

Result

[
  "HI"
]

SQL

select upper('hi');

select lower('HI');

Result

[
  "hi"
]

Trim, Ltrim,

Trim, ltrim, and rtrim strip surrounding whitespace.

SQL

select trim('  hi  ');

Result

[
  "hi"
]

SQL

select trim('  hi  ');

select ltrim('  hi');

Result

[
  "hi"
]

SQL

select trim('  hi  ');

select ltrim('  hi');

select rtrim('hi  ');

Result

[
  "hi"
]

Substr Is

Substr is 1-based, with an optional length.

SQL

select substr('hello', 2);

Result

[
  "ello"
]

SQL

select substr('hello', 2);

select substr('hello', 2, 3);

Result

[
  "ell"
]

SQL

select substr('hello', 2);

select substr('hello', 2, 3);

select substring('hello', 1, 1);

Result

[
  "h"
]

Replace Swaps

Replace swaps every occurrence of a substring.

SQL

select replace('aXbXc', 'X', '-');

Result

[
  "a-b-c"
]

Concat Joins

Concat joins arguments, skipping nulls, stringifying scalars.

SQL

select concat('a', 'b', 'c');

Result

[
  "abc"
]

SQL

select concat('a', 'b', 'c');

select concat('a', null, 'b');

Result

[
  "ab"
]

SQL

select concat('a', 'b', 'c');

select concat('a', null, 'b');

select concat('n', 42);

Result

[
  "n42"
]

Concat_ws Joins

Concat_ws joins with a separator, skipping nulls.

SQL

select concat_ws('-', 'a', 'b', 'c');

Result

[
  "a-b-c"
]

SQL

select concat_ws('-', 'a', 'b', 'c');

select concat_ws(',', 'a', null, 'b');

Result

[
  "a,b"
]

Repeat Duplicates

Repeat duplicates a string; reverse is dynamic on value.

SQL

select repeat('ab', 3);

Result

[
  "ababab"
]

SQL

select repeat('ab', 3);

select reverse('abc');

Result

[
  "cba"
]

SQL

select repeat('ab', 3);

select reverse('abc');

select reverse([1, 2, 3]);

Result

[
  [ 3, 2, 1 ]
]

Lpad And

Lpad and rpad pad to a target width with a fill string.

SQL

select lpad('5', 3, '0');

Result

[
  "005"
]

SQL

select lpad('5', 3, '0');

select rpad('5', 3, '0');

Result

[
  "500"
]

SQL

select lpad('5', 3, '0');

select rpad('5', 3, '0');

select lpad('x', 3);

Result

[
  "  x"
]

Repeat /

Repeat / lpad reject pathological sizes instead of exhausting memory.

SQL

select repeat('x', 99999999999);

Expected error: runtime

SQL

select repeat('x', 99999999999);

select lpad('x', 99999999999);

Expected error: runtime

Strpos /

Strpos / instr return a 1-based index, 0 if absent.

SQL

select strpos('hello', 'l');

Result

[
  3
]

SQL

select strpos('hello', 'l');

select instr('hello', 'l');

Result

[
  3
]

SQL

select strpos('hello', 'l');

select instr('hello', 'l');

select strpos('hello', 'z');

Result

[
  0
]

Starts_with, Ends_with,

Starts_with, ends_with, contains (dynamic on value).

SQL

select starts_with('hello', 'he');

Result

[
  true
]

SQL

select starts_with('hello', 'he');

select ends_with('hello', 'lo');

Result

[
  true
]

SQL

select starts_with('hello', 'he');

select ends_with('hello', 'lo');

select contains('hello', 'ell');

Result

[
  true
]

SQL

select starts_with('hello', 'he');

select ends_with('hello', 'lo');

select contains('hello', 'ell');

select contains([1, 2, 3], 2);

Result

[
  true
]

Array_length, Array_contains, Array_position

Array_length, array_contains, array_position.

SQL

select array_length([1, 2, 3]);

Result

[
  3
]

SQL

select array_length([1, 2, 3]);

select array_contains([1, 2, 3], 2);

Result

[
  true
]

SQL

select array_length([1, 2, 3]);

select array_contains([1, 2, 3], 2);

select array_position([10, 20, 30], 20);

Result

[
  2
]

SQL

select array_length([1, 2, 3]);

select array_contains([1, 2, 3], 2);

select array_position([10, 20, 30], 20);

select array_position([10, 20], 99);

Result

[
  0
]

Array_append, Array_prepend, Array_concat

Array_append, array_prepend, array_concat.

SQL

select array_append([1, 2], 3);

Result

[
  [ 1, 2, 3 ]
]

SQL

select array_append([1, 2], 3);

select array_prepend(0, [1, 2]);

Result

[
  [ 0, 1, 2 ]
]

SQL

select array_append([1, 2], 3);

select array_prepend(0, [1, 2]);

select array_concat([1, 2], [3, 4]);

Result

[
  [ 1, 2, 3, 4 ]
]

Array_reverse, Array_distinct,

Array_reverse, array_distinct, array_slice, array_to_string.

SQL

select array_reverse([1, 2, 3]);

Result

[
  [ 3, 2, 1 ]
]

SQL

select array_reverse([1, 2, 3]);

select array_distinct([1, 2, 2, 3, 1]);

Result

[
  [ 1, 2, 3 ]
]

SQL

select array_reverse([1, 2, 3]);

select array_distinct([1, 2, 2, 3, 1]);

select array_slice([1, 2, 3, 4, 5], 2, 4);

Result

[
  [ 2, 3, 4 ]
]

SQL

select array_reverse([1, 2, 3]);

select array_distinct([1, 2, 2, 3, 1]);

select array_slice([1, 2, 3, 4, 5], 2, 4);

select array_to_string([1, 2, 3], '-');

Result

[
  "1-2-3"
]

Object_keys, Object_values, Object_has_key

Object_keys, object_values, object_has_key.

SQL

select object_keys({a: 1, b: 2});

Result

[
  [ "a", "b" ]
]

SQL

select object_keys({a: 1, b: 2});

select object_values({a: 1, b: 2});

Result

[
  [ 1, 2 ]
]

SQL

select object_keys({a: 1, b: 2});

select object_values({a: 1, b: 2});

select object_has_key({a: 1}, 'a');

Result

[
  true
]

SQL

select object_keys({a: 1, b: 2});

select object_values({a: 1, b: 2});

select object_has_key({a: 1}, 'a');

select object_has_key({a: 1}, 'z');

Result

[
  false
]

Fn null Propagation

A null argument to a strict function yields null.

SQL

select abs(null);

Result

[
  null
]

SQL

select abs(null);

select upper(null);

Result

[
  null
]

SQL

select abs(null);

select upper(null);

select length(null);

Result

[
  null
]

SQL

select abs(null);

select upper(null);

select length(null);

select round(null, 2);

Result

[
  null
]

SQL

select abs(null);

select upper(null);

select length(null);

select round(null, 2);

select array_length(null);

Result

[
  null
]

Wrong Argument

Wrong argument count is a static error.

SQL

select abs(1, 2);

Expected error: static

SQL

select abs(1, 2);

select upper();

Expected error: static

An Undefined

An undefined function name is a static error.

SQL

select bogus(1);

Expected error: static

Fn Type Error

A wrong-typed argument is a runtime error.

SQL

select abs('x');

Expected error: runtime

SQL

select abs('x');

select upper(42);

Expected error: runtime