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.

### # 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)
``````
1
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)
``````
1

Absolute value.

``````CEIL(num_expr)
``````
1

The function application `CEIL(n)` returns the least integer not less than `n`.

``````FLOOR(num_expr)
``````
1

The function application `FLOOR(n)` returns the greatest integer not greater than `n`.

``````ROUND(num_expr)
``````
1

The function application `ROUND(n)` returns the nearest integer to `n` the even integer if `n` is equidistant between two integers.

``````SQRT(num_expr)
``````
1

The square root of a numeric value.

``````LOG(num_expr)
LOG2(num_expr)
LOG10(num_expr)
EXP(num_expr)
``````
1
2
3
4
``````SIGN(num_expr)
``````
1

The function application `SIGN(n)` returns the sign of a numeric value as an Integer.

• returns `-1` if `n` is negative
• returns `0` if `n` is exact zero
• returns `1` if `n` is positive
• returns `null` if `n` is exact `null`

### # 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)
``````
1
2
3
4
5
6
7
8
9

### # String Functions

``````TO_STR(val_expr)
``````
1

Convert a value expression to a readable string.

``````TO_LOWER(str)
``````
1

Convert a string to lower case, using simple case conversion.

``````TO_UPPER(str)
``````
1

Convert a string to upper case, using simple case conversion.

``````TRIM(str)
``````
1

Remove leading and trailing white space from a string.

``````LEFT_TRIM(str)
``````
1

Remove leading white space from a string.

``````RIGHT_TRIM(str)
``````
1

Remove trailing white space from a string.

``````REVERSE(str)
``````
1

Reverse the characters of a string.

``````STRLEN(str)
``````
1

Returns the number of characters in a string.

``````TAKE(num_expr, str)
``````
1

The function application `TAKE(n, s)` returns the prefix of the string of length `n`.

``````TAKEEND(num_expr, str)
``````
1

The function application `TAKEEND(n, s)` returns the suffix remaining after taking `n` characters from the end of the string.

``````DROP(num_expr, str)
``````
1

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)
``````
1

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)
``````
1

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)
``````
1

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 NameRaw 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)
``````
1

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)
``````
1

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)
``````
1

Given an array, checks if the search value is contained in the array (of the same type).

``````ARRAY_DISTINCT(arr_expr)
``````
1

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)
``````
1

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)
``````
1

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)
``````
1

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)
``````
1
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)
``````
1

Returns the length of a finite list.

Returns `NULL` if the argument is `NULL`.

``````ARRAY_MAX(arr_expr)
``````
1

Returns the maximum value from within a given array of elements.

Returns `NULL` if the argument is `NULL`.

``````ARRAY_MIN(arr_expr)
``````
1

Returns the minimum value from within a given array of elements.

Returns `NULL` if the argument is `NULL`.

``````ARRAY_REMOVE(arr_expr, val_expr)
``````
1

Removes all elements from the input array equal to the second argument.

Returns `NULL` if the first argument is `NULL`.

``````ARRAY_SORT(arr_expr)
``````
1

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