# Scalar Functions
Scalar functions operate on one or more values and then return a single value. They can be used wherever a value expression is valid.
Scalar functions are divided into serval kinds.
# Type Casting Functions
Our SQL supports explicit type casting in the form of CAST(expr, type)
or expr :: type
. Target type can be one of the follows:
INTEGER
FLOAT
NUMERIC
BOOLEAN
BYTEA
STRING
DATE
TIME
TIMESTAMP
INTERVAL
JSONB
<type>[]
(array)MAP[<key_type> => <value_type>]
(map)
# JSON Functions
To use JSON data conveniently, we support the following functions:
<json> -> <text>
, which gets the corresponded field and return as JSON format.<json> ->> <text>
, which gets the corresponded field and return as text format.<json> #> <array_of_text/int>
, which gets the corresponded field in the specified path and return as JSON format.<json> #>> <array_of_text/int>
, which gets the corresponded field in the specified path and return as text format.
# Array and Map Accessing Functions
To access fields of arrays and maps, we support the following functions:
<array> [<index>]
,<array> [<start_index>:]
,<array> [:<end_index>]
and<array> [<start_index>:<end_index>]
<map> => <key_expr>
# Trigonometric Functions
All trigonometric functions perform a calculation, operate on a single numeric value and then return a single numeric value.
For values outside the domain, NaN
is returned.
SIN(num_expr)
SINH(num_expr)
ASIN(num_expr)
ASINH(num_expr)
COS(num_expr)
COSH(num_expr)
ACOS(num_expr)
ACOSH(num_expr)
TAN(num_expr)
TANH(num_expr)
ATAN(num_expr)
ATANH(num_expr)
2
3
4
5
6
7
8
9
10
11
12
# Arithmetic Functions
The following functions perform a calculation, operate on a single numeric value and then return a single numeric value.
ABS(num_expr)
Absolute value.
CEIL(num_expr)
The function application CEIL(n)
returns the least integer not less than n
.
FLOOR(num_expr)
The function application FLOOR(n)
returns the greatest integer not greater than n
.
ROUND(num_expr)
The function application ROUND(n)
returns the nearest integer to n
the even integer if n
is equidistant between two integers.
SQRT(num_expr)
The square root of a numeric value.
LOG(num_expr)
LOG2(num_expr)
LOG10(num_expr)
EXP(num_expr)
2
3
4
SIGN(num_expr)
The function application SIGN(n)
returns the sign of a numeric value as an Integer.
- returns
-1
ifn
is negative - returns
0
ifn
is exact zero - returns
1
ifn
is positive - returns
null
ifn
is exactnull
# Predicate Functions
Function applications of the form IS_A(x)
where A
is the name of a type returns TRUE
if the argument x
is of type A
, otherwise FALSE
.
IS_INT(val_expr)
IS_FLOAT(val_expr)
IS_NUM(val_expr)
IS_BOOL(val_expr)
IS_STR(val_expr)
IS_MAP(val_expr)
IS_ARRAY(val_expr)
IS_DATE(val_expr)
IS_TIME(val_expr)
2
3
4
5
6
7
8
9
# String Functions
TO_STR(val_expr)
Convert a value expression to a readable string.
TO_LOWER(str)
Convert a string to lower case, using simple case conversion.
TO_UPPER(str)
Convert a string to upper case, using simple case conversion.
TRIM(str)
Remove leading and trailing white space from a string.
LEFT_TRIM(str)
Remove leading white space from a string.
RIGHT_TRIM(str)
Remove trailing white space from a string.
REVERSE(str)
Reverse the characters of a string.
STRLEN(str)
Returns the number of characters in a string.
TAKE(num_expr, str)
The function application TAKE(n, s)
returns the prefix of the string of length n
.
TAKEEND(num_expr, str)
The function application TAKEEND(n, s)
returns the suffix remaining after taking n
characters from the end of the string.
DROP(num_expr, str)
The function application DROP(n, s)
returns the suffix of the string after the first n
characters, or the empty string if n is greater than the length of the string.
DROPEND(num_expr, str)
The function application DROPEND(n, s)
returns the prefix remaining after dropping n
characters from the end of the string.
# Null Functions
IFNULL(val_expr, val_expr)
The function application IFNULL(x, y)
returns y
if x
is NULL
, otherwise x
.
When the argument type is a complex type, for example, ARRAY
or MAP
, the contents of the complex type are not inspected.
NULLIF(val_expr, val_expr)
The function application NULLIF(x, y)
returns NULL
if x
is equal to y
, otherwise x
.
When the argument type is a complex type, for example, ARRAY
or MAP
, the contents of the complex type are not inspected.
# Time and Date Functions
# Time Format
Formats are analogous to strftime (opens new window).
Format Name | Raw Format String |
---|---|
simpleDateFormat | "%Y-%m-%d %H:%M:%S" |
iso8061DateFormat | "%Y-%m-%dT%H:%M:%S%z" |
webDateFormat | "%a, %d %b %Y %H:%M:%S GMT" |
mailDateFormat | "%a, %d %b %Y %H:%M:%S %z" |
DATETOSTRING(val_expr, str)
Formatting seconds since 1970-01-01 00:00:00 UTC to string in GMT with the second string argument as the given format name.
STRINGTODATE(str, str)
Formatting string to seconds since 1970-01-01 00:00:00 UTC in GMT with the second string argument as the given format name.
# Array Functions
ARRAY_CONTAINS(arr_expr, val_expr)
Given an array, checks if the search value is contained in the array (of the same type).
ARRAY_DISTINCT(arr_expr)
Returns an array of all the distinct values, including NULL
if present, from the input array. The output array elements are in order of their first occurrence in the input.
Returns NULL
if the argument is NULL
.
ARRAY_EXCEPT(arr_expr, arr_expr)
Returns an array of all the distinct elements from an array, except for those also present in a second array. The order of entries in the first array is preserved but duplicates are removed.
Returns NULL
if either input is NULL
.
ARRAY_INTERSECT(arr_expr, arr_expr)
Returns an array of all the distinct elements from the intersection of both input arrays. If the first list contains duplicates, so will the result. If the element is found in both the first and the second list, the element from the first list will be used.
Returns NULL
if either input is NULL
.
ARRAY_UNION(arr_expr, arr_expr)
Returns the array union of the two arrays. Duplicates, and elements of the first list, are removed from the second list, but if the first list contains duplicates, so will the result.
Returns NULL
if either input is NULL
.
ARRAY_JOIN(arr_expr)
ARRAY_JOIN(arr_expr, str)
2
Creates a flat string representation of all the primitive elements contained in the given array. The elements in the resulting string are separated by the chosen delimiter, which is an optional parameter that falls back to a comma ,
.
ARRAY_LENGTH(arr_expr)
Returns the length of a finite list.
Returns NULL
if the argument is NULL
.
ARRAY_MAX(arr_expr)
Returns the maximum value from within a given array of elements.
Returns NULL
if the argument is NULL
.
ARRAY_MIN(arr_expr)
Returns the minimum value from within a given array of elements.
Returns NULL
if the argument is NULL
.
ARRAY_REMOVE(arr_expr, val_expr)
Removes all elements from the input array equal to the second argument.
Returns NULL
if the first argument is NULL
.
ARRAY_SORT(arr_expr)
Sort an array. Elements are arranged from lowest to highest, keeping duplicates in the order they appeared in the input.
Returns NULL
if the first argument is NULL
.