Malloy has a standard set of functions available at all times. These include scalar functions (e.g. concat
), aggregate functions (e.g. stddev
), and analytic (or "window") functions (e.g. lag
).
These functions are defined in Malloy in order to provide basic typechecking of arguments and return types, as well as some compatibility transformations. Perfect compatibility across all dialects for all functions is not guaranteed, and specific differences are noted on a case-by-case basis.
For quick function lookup, see the alphebetized table of all available functions.
Note: the behavior of functions changed in v0.0.39. For more information, see a description of that change.
Syntax
Built-In Functions
Functions that are "built-in" to Malloy can be called the usual way with the name of the function, an open parenthesis, a comma separated list of Malloy expressions, and a close parenthesis, e.g. concat(upper(first), ' ', upper(last))
.
Raw SQL Functions
Functions that are not built in may be called in the same way, but with an !
in between the function name and argument list: sinh!(x)
. In this case, Malloy assumes the return type of the function is the same as that of the first argument (or number
if there are no arguments). When this is not correct, the return type can be specified after the !
, e.g. hash!number(username)
.
Function Documentation
Section |
---|
String Functions |
Numeric Functions |
Interval Functions |
Date and Timestamp Functions |
Other Functions |
Aggregate Functions |
Ungrouped Aggregate Functions |
Analytic Functions |
String Functions
ascii | byte_length | chr | concat | ends_with |
length | lower | ltrim | regexp_extract | repeat |
replace | repeat | rtrim | starts_with | strpos |
substr | trim | unicode | upper |
concat
concat(value, ...)
Concatenates multiple values together, casting non-string
values to string
. The exact behavior of string casting may depend on the dialect.
If no values are given, concat
returns the empty string.
Behavior for null
depends on dialect: in BigQuery, if any argument is null
, the result is null
; in DuckDB and Postgres, null
is treated as an empty string.
run: empty -> { group_by: e1 is concat('foo', 'bar') e2 is concat(1, null) e4 is concat('Date: ', @2021-01-23) e3 is concat() }
[ { "e1": "foobar", "e2": "1", "e4": "Date: 2021-01-23", "e3": "" } ]
SELECT CONCAT('foo','bar') as "e1", CONCAT(1,NULL) as "e2", CONCAT('Date: ',DATE '2021-01-23') as "e4", '' as "e3" FROM ( SELECT 1 ) as base GROUP BY 1,2,3,4 ORDER BY 1 asc NULLS LAST
lower
lower(value)
Returns a string like value
but with all alphabetic characters in lowercase.
run: empty -> { group_by: e1 is lower('FOO') }
[ { "e1": "foo" } ]
SELECT LOWER('FOO') as "e1" FROM ( SELECT 1 ) as base GROUP BY 1 ORDER BY 1 asc NULLS LAST
upper
upper(value)
Returns a string like value
but with all alphabetic characters in uppercase.
run: empty -> { group_by: e1 is upper('foo') }
[ { "e1": "FOO" } ]
SELECT UPPER('foo') as "e1" FROM ( SELECT 1 ) as base GROUP BY 1 ORDER BY 1 asc NULLS LAST
strpos
strpos(test_string, search_string)
Returns the 1-based position of the first occurrence of search_string
in test_string
, or 0
if search_string
is not found.
run: empty -> { group_by: e1 is strpos('abc', 'b') e2 is strpos('abc', 'd') e3 is strpos('abc', null) e4 is strpos(null, 'a') }
[ { "e1": 2, "e2": 0, "e3": null, "e4": null } ]
SELECT STRPOS('abc','b') as "e1", STRPOS('abc','d') as "e2", STRPOS('abc',NULL) as "e3", STRPOS(NULL,'a') as "e4" FROM ( SELECT 1 ) as base GROUP BY 1,2,3,4 ORDER BY 1 asc NULLS LAST
starts_with
starts_with(value, prefix)
Returns true
if value
begins with prefix
and false
otherwise. If either value
or prefix
is null
, the result is false
(unlike in SQL, where it would be null
).
run: empty -> { group_by: e1 is starts_with('abc', 'a') e2 is starts_with('abc', 'b') e3 is starts_with('abc', null) e4 is starts_with(null, 'a') }
[ { "e1": true, "e2": false, "e3": false, "e4": false } ]
SELECT COALESCE(STARTS_WITH('abc', 'a'), false) as "e1", COALESCE(STARTS_WITH('abc', 'b'), false) as "e2", COALESCE(STARTS_WITH('abc', NULL), false) as "e3", COALESCE(STARTS_WITH(NULL, 'a'), false) as "e4" FROM ( SELECT 1 ) as base GROUP BY 1,2,3,4 ORDER BY 1 asc NULLS LAST
ends_with
ends_with(value, suffix)
Returns true
if value
ends with prefix
and false
otherwise. If either value
or suffix
is null
, the result is false
(unlike in SQL, where it would be null
).