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
).
run: empty -> { group_by: e1 is ends_with('abc', 'c') e2 is ends_with('abc', 'b') e3 is ends_with('abc', null) e4 is ends_with(null, 'a') }
[ { "e1": true, "e2": false, "e3": false, "e4": false } ]
SELECT COALESCE(SUFFIX('abc', 'c'), false) as "e1", COALESCE(SUFFIX('abc', 'b'), false) as "e2", COALESCE(SUFFIX('abc', NULL), false) as "e3", COALESCE(SUFFIX(NULL, 'a'), false) as "e4" FROM ( SELECT 1 ) as base GROUP BY 1,2,3,4 ORDER BY 1 asc NULLS LAST
trim
trim(value) trim(value, trim_characters)
Returns a string with leading and trailing characters in trim_characters
(or whitespace, if trim_characters
is unspecified) removed.
run: empty -> { group_by: e1 is trim(' keep this ') e2 is trim('_ _keep_this_ _', '_ ') e3 is trim(' keep everything ', '') e4 is trim('null example', null) e5 is trim(null, ' _') }
[ { "e1": "keep this", "e2": "keep_this", "e3": " keep everything ", "e4": null, "e5": null } ]
SELECT TRIM(' keep this ') as "e1", TRIM('_ _keep_this_ _','_ ') as "e2", TRIM(' keep everything ','') as "e3", TRIM('null example',NULL) as "e4", TRIM(NULL,' _') as "e5" FROM ( SELECT 1 ) as base GROUP BY 1,2,3,4,5 ORDER BY 1 asc NULLS LAST
ltrim
ltrim(value) ltrim(value, trim_characters)
Like trim(value, trim_characters)
but only removes leading characters.
run: empty -> { group_by: e1 is ltrim(' keep this -> ') e2 is ltrim('_ _keep_this -> _ _', '_ ') e3 is ltrim(' keep everything ', '') e4 is ltrim('null example', null) e5 is ltrim(null, ' _') }
[ { "e1": "keep this -> ", "e2": "keep_this -> _ _", "e3": " keep everything ", "e4": null, "e5": null } ]
SELECT LTRIM(' keep this -> ') as "e1", LTRIM('_ _keep_this -> _ _','_ ') as "e2", LTRIM(' keep everything ','') as "e3", LTRIM('null example',NULL) as "e4", LTRIM(NULL,' _') as "e5" FROM ( SELECT 1 ) as base GROUP BY 1,2,3,4,5 ORDER BY 1 asc NULLS LAST
rtrim
rtrim(value) rtrim(value, trim_characters)
Like trim(value, trim_characters)
but only removes trailing characters.
run: empty -> { group_by: e1 is rtrim(' <- keep this ') e2 is rtrim('_ _ <- keep_this _ _', '_ ') e3 is rtrim(' keep everything ', '') e4 is rtrim('null example', null) e5 is rtrim(null, ' _') }
[ { "e1": " <- keep this", "e2": "_ _ <- keep_this", "e3": " keep everything ", "e4": null, "e5": null } ]
SELECT RTRIM(' <- keep this ') as "e1", RTRIM('_ _ <- keep_this _ _','_ ') as "e2", RTRIM(' keep everything ','') as "e3", RTRIM('null example',NULL) as "e4", RTRIM(NULL,' _') as "e5" FROM ( SELECT 1 ) as base GROUP BY 1,2,3,4,5 ORDER BY 1 asc NULLS LAST
substr
substr(value, position) substr(value, position, length)
Returns a substring of value
starting at the 1-based index position
. If length
is specified, the returned string will be at most length
characters long; otherwise the returned string will extend to the end of value
. Negative values of position
index the starting value from the end of the string (with -1
for the last character of the string). A position
of 0
is equivalent to a position
of 1
.
run: empty -> { group_by: e1 is substr('123456789', 1) e2 is substr('123456789', 0) e3 is substr('123456789', 5) e4 is substr('123456789', -4) e5 is substr('123456789', 1, 3) e6 is substr('123456789', -5, 3) e7 is substr('123456789', null, 1) e8 is substr('123456789', 1, null) e9 is substr(null, 1, 1) }
[ { "e1": "123456789", "e2": "123456789", "e3": "56789", "e4": "6789", "e5": "123", "e6": "567", "e7": null, "e8": null, "e9": null } ]
SELECT SUBSTR('123456789',1) as "e1", SUBSTR('123456789',0) as "e2", SUBSTR('123456789',5) as "e3", SUBSTR('123456789',-4) as "e4", SUBSTR('123456789',1,3) as "e5", SUBSTR('123456789',-5,3) as "e6", SUBSTR('123456789',NULL,1) as "e7", SUBSTR('123456789',1,NULL) as "e8", SUBSTR(NULL,1,1) as "e9" FROM ( SELECT 1 ) as base GROUP BY 1,2,3,4,5,6,7,8,9 ORDER BY 1 asc NULLS LAST
regexp_extract
regexp_extract(value, pattern)
Returns the first substring of value
which matches pattern
. Returns null
if there is no matching substring.
run: empty -> { group_by: e1 is regexp_extract('the Malloy language', r'M....y') e2 is regexp_extract('the SQL language', r'M....y') e3 is regexp_extract('the null language', null) e4 is regexp_extract(null, r'nothing') }
[ { "e1": "Malloy", "e2": "", "e3": null, "e4": null } ]
SELECT REGEXP_EXTRACT('the Malloy language','M....y') as "e1", REGEXP_EXTRACT('the SQL language','M....y') as "e2", REGEXP_EXTRACT('the null language',NULL) as "e3", REGEXP_EXTRACT(NULL,'nothing') as "e4" FROM ( SELECT 1 ) as base GROUP BY 1,2,3,4 ORDER BY 1 asc NULLS LAST
replace
replace(value, pattern, replacement)
Returns a copy of value
with all occurrences of pattern
replaced with replacement
.
If pattern
is empty, no replacement occurs and value
is returned unchanged.
If pattern
is a regular expression, parenthesized capturing groups can be included in the replacement
string with '\\1'
to '\\9'
. In BigQuery and DuckDB, the full matched string can be referenced with '\\0'
.
run: empty -> { group_by: e1 is replace('the SQL language', 'SQL', 'Malloy') e2 is replace('the SQL language', r'S.L', 'Malloy') e3 is replace('SQL SQL SQL', 'SQL', 'Malloy') e4 is replace('SQL SQL SQL', r'S.L', 'Malloy') e5 is replace(null, 'SQL', 'Malloy') e6 is replace('the null language', null, 'Malloy') e7 is replace('the null language', 'SQL', null) e8 is replace('the language', '', 'Malloy') e9 is replace('axbxc', r'(a).(b).(c)', '\\0 - \\1 - \\2 - \\3') }
[ { "e1": "the Malloy language", "e2": "the Malloy language", "e3": "Malloy Malloy Malloy", "e4": "Malloy Malloy Malloy", "e5": null, "e6": null, "e7": null, "e8": "the language", "e9": "axbxc - a - b - c" } ]
SELECT REPLACE('the SQL language','SQL','Malloy') as "e1", REGEXP_REPLACE('the SQL language', 'S.L', 'Malloy', 'g') as "e2", REPLACE('SQL SQL SQL','SQL','Malloy') as "e3", REGEXP_REPLACE('SQL SQL SQL', 'S.L', 'Malloy', 'g') as "e4", REPLACE(NULL,'SQL','Malloy') as "e5", REPLACE('the null language',NULL,'Malloy') as "e6", REPLACE('the null language','SQL',NULL) as "e7", REPLACE('the language','','Malloy') as "e8", REGEXP_REPLACE('axbxc', '(a).(b).(c)', '\0 - \1 - \2 - \3', 'g') as "e9" FROM ( SELECT 1 ) as base GROUP BY 1,2,3,4,5,6,7,8,9 ORDER BY 1 asc NULLS LAST
length
length(value)
Returns the number of characters in value
.
run: empty -> { group_by: e1 is length('Malloy') e2 is length('') e3 is length('𝔐𝔞𝔩𝔩𝔬𝔶') e4 is length(null) }
[ { "e1": 6, "e2": 0, "e3": 6, "e4": null } ]
SELECT LENGTH('Malloy') as "e1", LENGTH('') as "e2", LENGTH('𝔐𝔞𝔩𝔩𝔬𝔶') as "e3", LENGTH(NULL) as "e4" FROM ( SELECT 1 ) as base GROUP BY 1,2,3,4 ORDER BY 1 asc NULLS LAST
byte_length
byte_length(value)
Returns the number of bytes in the unicode encoding of value
.
run: empty -> { group_by: e1 is byte_length('Malloy') e2 is byte_length('') e3 is byte_length('𝔐𝔞𝔩𝔩𝔬𝔶') e4 is byte_length(null) }
[ { "e1": 6, "e2": 0, "e3": 24, "e4": null } ]
SELECT (BIT_LENGTH('Malloy') / 8) as "e1", (BIT_LENGTH('') / 8) as "e2", (BIT_LENGTH('𝔐𝔞𝔩𝔩𝔬𝔶') / 8) as "e3", (BIT_LENGTH(NULL) / 8) as "e4" FROM ( SELECT 1 ) as base GROUP BY 1,2,3,4 ORDER BY 1 asc NULLS LAST
chr
chr(value)
Returns a unicode code point value
and returns a string containing the character with the matching code point. If value
is 0, returns an empty string not a string containing the null character.
run: empty -> { group_by: e1 is chr(65) e2 is chr(255) e3 is chr(0) e4 is chr(null) }
[ { "e1": "A", "e2": "ÿ", "e3": "", "e4": null } ]
SELECT CASE WHEN 65 = 0 THEN '' ELSE CHR(65) END as "e1", CASE WHEN 255 = 0 THEN '' ELSE CHR(255) END as "e2", CASE WHEN 0 = 0 THEN '' ELSE CHR(0) END as "e3", CASE WHEN NULL = 0 THEN '' ELSE CHR(NULL) END as "e4" FROM ( SELECT 1 ) as base GROUP BY 1,2,3,4 ORDER BY 1 asc NULLS LAST
Returns an error if value
is not a valid unicode code point.
ascii
ascii(value)
Returns the ASCII code point of the first character of value
. If value
is empty, returns 0.
In BigQuery, ascii
errors if the first character of value
is not representable in ASCII. In Postgres and DuckDB, ascii
returns the Unicode code point of the first character.
run: empty -> { group_by: e1 is ascii('Malloy') e2 is ascii('') e3 is ascii(null) }
[ { "e1": 77, "e2": 0, "e3": null } ]
SELECT ASCII('Malloy') as "e1", ASCII('') as "e2", ASCII(NULL) as "e3" FROM ( SELECT 1 ) as base GROUP BY 1,2,3 ORDER BY 1 asc NULLS LAST
unicode
unicode(value)
Returns the Unicode code point of the first character of value
. If value
is empty, returns 0.
run: empty -> { group_by: e1 is unicode('Malloy') e2 is unicode('𝔐𝔞𝔩𝔩𝔬𝔶') e3 is unicode('') e4 is unicode(null) }
[ { "e1": 77, "e2": 120080, "e3": 0, "e4": null } ]
SELECT CASE WHEN 'Malloy' = '' THEN 0 ELSE UNICODE('Malloy') END as "e1", CASE WHEN '𝔐𝔞𝔩𝔩𝔬𝔶' = '' THEN 0 ELSE UNICODE('𝔐𝔞𝔩𝔩𝔬𝔶') END as "e2", CASE WHEN '' = '' THEN 0 ELSE UNICODE('') END as "e3", CASE WHEN NULL = '' THEN 0 ELSE UNICODE(NULL) END as "e4" FROM ( SELECT 1 ) as base GROUP BY 1,2,3,4 ORDER BY 1 asc NULLS LAST
repeat
repeat(value, num_repetitions)
Return a string consisting of value
repeated num_repetitions
times.
Undefined behavior if num_repetitions
is negative or a non-integer.
run: empty -> { group_by: e1 is repeat('(A)', 3) e2 is repeat('(A)', 1) e3 is repeat('(A)', 0) e4 is repeat('', 3) e5 is repeat(null, 1) e6 is repeat('(A)', null) }
[ { "e1": "(A)(A)(A)", "e2": "(A)", "e3": "", "e4": "", "e5": null, "e6": null } ]
SELECT REPEAT('(A)',3) as "e1", REPEAT('(A)',1) as "e2", REPEAT('(A)',0) as "e3", REPEAT('',3) as "e4", REPEAT(NULL,1) as "e5", REPEAT('(A)',NULL) as "e6" FROM ( SELECT 1 ) as base GROUP BY 1,2,3,4,5,6 ORDER BY 1 asc NULLS LAST
reverse
reverse(value)
Return a copy of value
with the characters in reverse order.
run: empty -> { group_by: e1 is reverse('Malloy') e2 is reverse('𝔶𝔬𝔩𝔩𝔞𝔐') e3 is reverse('racecar') e4 is reverse('') e5 is reverse(null) }
[ { "e1": "yollaM", "e2": "𝔐𝔞𝔩𝔩𝔬𝔶", "e3": "racecar", "e4": "", "e5": null } ]
SELECT REVERSE('Malloy') as "e1", REVERSE('𝔶𝔬𝔩𝔩𝔞𝔐') as "e2", REVERSE('racecar') as "e3", REVERSE('') as "e4", REVERSE(NULL) as "e5" FROM ( SELECT 1 ) as base GROUP BY 1,2,3,4,5 ORDER BY 1 asc NULLS LAST
Numeric Functions
abs | acos | asin | atan | atan2 | ceil |
cos | div | exp | floor | is_inf | is_nan |
log | ln | pi | pow | rand | round |
sign | sin | sqrt | tan | trunc |
round
round(value) round(value, precision)
Round value
to the nearest integer, or if precision
is specified, round to precision
decimal places right of the decimal. If precision
is negative, round to a precision that many places to the left of the decimal. Halfway cases are rounded away from zero.
run: empty -> { group_by: e1 is round(1.49) e2 is round(1.51) e3 is round(1.5) e4 is round(-1.5) e5 is round(1.5, 0) e6 is round(1.551, 1) e7 is round(14.12, -1) e8 is round(1.4, null) e9 is round(null, 1) }
[ { "e1": 1, "e2": 2, "e3": 2, "e4": -2, "e5": 2, "e6": 1.6, "e7": 10, "e8": null, "e9": null } ]
SELECT ROUND(1.49::DOUBLE) as "e1", ROUND(1.51::DOUBLE) as "e2", ROUND(1.5::DOUBLE) as "e3", ROUND(-1.5::DOUBLE) as "e4", ROUND(1.5::DOUBLE,0) as "e5", ROUND(1.551::DOUBLE,1) as "e6", ROUND(14.12::DOUBLE,-1) as "e7", ROUND(1.4::DOUBLE,NULL) as "e8", ROUND(NULL,1) as "e9" FROM ( SELECT 1 ) as base GROUP BY 1,2,3,4,5,6,7,8,9 ORDER BY 1 asc NULLS LAST
trunc
trunc(value) trunc(value, precision)
Truncate value
to an integer, or if precision
is specified, truncate after that many decimal places. If precision
is negative, truncate to that many decimal places to the left of the decimal point. Similar to round(value, precision)
but always rounds toward zero.
run: empty -> { group_by: e1 is trunc(1.49) e2 is trunc(1.51) e3 is trunc(1.5) e4 is trunc(-1.5) e5 is trunc(1.5, 0) e6 is trunc(1.551, 1) e7 is trunc(19.999, -1) e8 is trunc(1.4, null) e9 is trunc(null, 1) }
[ { "e1": 1, "e2": 1, "e3": 1, "e4": -1, "e5": 1, "e6": 1.5, "e7": 10, "e8": null, "e9": null } ]
SELECT CASE WHEN 1.49::DOUBLE < 0 THEN CEIL(1.49::DOUBLE) ELSE FLOOR(1.49::DOUBLE) END as "e1", CASE WHEN 1.51::DOUBLE < 0 THEN CEIL(1.51::DOUBLE) ELSE FLOOR(1.51::DOUBLE) END as "e2", CASE WHEN 1.5::DOUBLE < 0 THEN CEIL(1.5::DOUBLE) ELSE FLOOR(1.5::DOUBLE) END as "e3", CASE WHEN -1.5::DOUBLE < 0 THEN CEIL(-1.5::DOUBLE) ELSE FLOOR(-1.5::DOUBLE) END as "e4", CASE WHEN 1.5::DOUBLE < 0 THEN CEIL(1.5::DOUBLE * POW(10, 0)) / POW(10, 0) ELSE FLOOR(1.5::DOUBLE * POW(10, 0)) / POW(10, 0) END as "e5", CASE WHEN 1.551::DOUBLE < 0 THEN CEIL(1.551::DOUBLE * POW(10, 1)) / POW(10, 1) ELSE FLOOR(1.551::DOUBLE * POW(10, 1)) / POW(10, 1) END as "e6", CASE WHEN 19.999::DOUBLE < 0 THEN CEIL(19.999::DOUBLE * POW(10, -1)) / POW(10, -1) ELSE FLOOR(19.999::DOUBLE * POW(10, -1)) / POW(10, -1) END as "e7", CASE WHEN 1.4::DOUBLE < 0 THEN CEIL(1.4::DOUBLE * POW(10, NULL)) / POW(10, NULL) ELSE FLOOR(1.4::DOUBLE * POW(10, NULL)) / POW(10, NULL) END as "e8", CASE WHEN NULL < 0 THEN CEIL(NULL * POW(10, 1)) / POW(10, 1) ELSE FLOOR(NULL * POW(10, 1)) / POW(10, 1) END as "e9" FROM ( SELECT 1 ) as base GROUP BY 1,2,3,4,5,6,7,8,9 ORDER BY 1 asc NULLS LAST
floor
floor(value)
Round value
down to the greatest integer not larger than value
.
run: empty -> { group_by: a is floor(1.0) b is floor(1.9) c is floor(-1.1) d is floor(null) }
[ { "a": 1, "b": 1, "c": -2, "d": null } ]
SELECT FLOOR(1.0::DOUBLE) as "a", FLOOR(1.9::DOUBLE) as "b", FLOOR(-1.1::DOUBLE) as "c", FLOOR(NULL) as "d" FROM ( SELECT 1 ) as base GROUP BY 1,2,3,4 ORDER BY 1 asc NULLS LAST
ceil
ceil(value)
Round value
up to the smallest integer not less than value
.