Functions available for Custom Formulas

There are three actions that accept custom formulas entered by users:

  1. Create a new column with a formula
  2. Transform columns with a formula
  3. Filter rows with a formula

These actions support the use of the following types of operators:

  1. Arithmetic: +, -, /, *, %
  2. Boolean: AND, OR, NOT. Valid forms are x AND y, x OR y, NOT y as well as AND(x, y), OR(x, y), NOT(x).
  3. Conditional: ==, !=, >, <, >=, <=. Note that a single = is admitted in place of ==, as well as <> in place of !=.
  4. Null predicate:: IS NULL, IS NOT NULL. Valid forms are x IS NULL, x IS NOT NULL as well as ISNULL(x) and ISNOTNULL(x).

The following set of functions can be used in the expressions accepted by any of the aforementioned three actions:

Name Description Function signatures
1 DAY The day of month as a number (DateTime) -> Numeric
2 MONTH The month of year as a number (DateTime) -> Numeric
3 YEAR The year as a number (DateTime) -> Numeric
4 HOUR The hour of the day (DateTime) -> Numeric
5 MINUTE The minute of hour (DateTime) -> Numeric
6 SECOND The second of minute (DateTime) -> Numeric
7 WEEKDAY The day of week as a number from 0 = Monday to 6 = Sunday (DateTime) -> Numeric
8 WEEKOFYEAR The week of year as a number from 1 to 52 (DateTime) -> Numeric
9 QUARTER The quarter in the year as a number from 1 to 4 (DateTime) -> Numeric
10 DATETIME_VALUE Returns datetime for an ISO-format string literal (STRING literal) -> DateTime
11 WEEKDAY_NAME Name of the day in week ("Sunday" etc.) (DateTime) -> Text
12 MONTH_NAME Name of the month ("January" etc.) (DateTime) -> Text
13 WEEKDAY_NAME_ABBREV Abbreviated name of the day in week ("Sun" etc.) (DateTime) -> Text
14 MONTH_NAME_ABBREV Abbreviated name of the month ("Jan" etc.) (DateTime) -> Text
15 LENGTH Number of characters in the string (Text) -> Numeric
16 LOWER Convert string to lower case (Text) -> Text
17 UPPER Convert string to upper case (Text) -> Text
18 INITCAP Change the first character in each value to upper case (Text) -> Text
19 TRIM Remove leading and trailing whitespaces (Text) -> Text
20 LTRIM Remove leading whitespaces (Text) -> Text
21 RTRIM Remove trailing whitespaces (Text) -> Text
22 LEFT First n characters of the string (Text, Numeric) -> Text
23 RIGHT Last n characters of the string (Text, Numeric) -> Text
24 SUBSTRING The part of the string starting at the given position to the end or up to the specified length. (Text, Numeric) -> Text
(Text, Numeric, Numeric) -> Text
25 FORMAT_NUMBER Format the number to a string to specified decimal places, commas separating 1000's (Numeric, INT literal) -> Text
26 RLIKE Indicates whether column or value (first argument) matches the given regular expression (second argument). Second argument must be a string literal and not a column. (Text, STRING literal) -> Boolean
27 SPLIT_DELIM Splits strings by a literal delimiter to produce a JSON-serialized array. The delimiter is interpreted as a literal rather than as a regular expression. The delimiter can be a maximum of 32 characters long. (Text, STRING literal) -> JSONArray
28 SPLIT_PATTERN Splits strings by a regex pattern to produce a JSON-serialized array. The pattern is interpreted as a regex pattern rather than as a literal delimiter. The pattern can be a maximum of 256 characters long. (Text, STRING literal) -> JSONArray
29 IS_NUMERIC Indicates whether string value is parsable to numeric (Text) -> Boolean
30 IS_DATETIME Indicates whether string value is parsable to datetime of given strftime format (Text, STRING literal) -> Boolean
31 TO_NUMERIC Casts string values to numeric, returns null if value is not parsable (Text) -> Numeric
32 TO_DATETIME Casts string values to datetime taking an strftime format string as second argument. Returns null if value is not parsable. (Text, STRING literal) -> DateTime
33 TO_TIMESTAMP Casts string values to datetime taking a Java Datetime format string as second argument. (Text, STRING literal) -> DateTime
34 IF When condition (first argument) is true, evaluates the second argument. When condition is not true, evaluates the third argument. (Boolean, Numeric, Numeric) -> Numeric
(Boolean, Text, Text) -> Text
(Boolean, Boolean, Boolean) -> Boolean
(Boolean, DateTime, DateTime) -> DateTime
(Boolean, JSONObject, JSONObject) -> JSONObject
(Boolean, JSONArray, JSONArray) -> JSONArray
35 E Returns the value of "e", the base of natural logarithm () -> Numeric
36 PI Returns the value of pi () -> Numeric
37 RAND Generates uniform random numbers between 0 and 1 () -> Numeric
(INT literal) -> Numeric
38 RANDN Generates random numbers from the standard normal distribution () -> Numeric
(INT literal) -> Numeric
39 ABS Absolute value of a number (Numeric) -> Numeric
40 ACOS Inverse cosine function. Computes the angle (in radians) whose cosine equals the input value. Returns null if input is out of [-1, 1] range (Numeric) -> Numeric
41 ACOSH Inverse hyperbolic cosine of a given input value.Returns null if input is negative. (Numeric) -> Numeric
42 ASIN Inverse sine function. Computes the angle (in radians) whose sine equals the input value. Returns null if input is out of [-1, 1] range (Numeric) -> Numeric
43 ASINH Inverse hyperbolic sine of a given value. (Numeric) -> Numeric
44 ATAN Arc tangent function. Computes the angle (in radians) whose tangent equals the input value. Returns null if input is out of [-1, 1] range (Numeric) -> Numeric
45 ATANH Inverse hyperbolic tangent of a given input value. Returns null if input is not between -1 and 1. (Numeric) -> Numeric
46 COS Trigonometric cosine (Numeric) -> Numeric
47 COSH Hyperbolic cosine (Numeric) -> Numeric
48 CEIL Ceiling of a number, i.e. the smallest integer not less than the input (Numeric) -> Numeric
49 DEGREES Computes the angle in degrees given value in radians (Numeric) -> Numeric
50 EXP Computes the exponential of a given value. (Numeric) -> Numeric
51 FACTORIAL Factorial of a given value. Fractional parts are truncated and negative input yields null. (Numeric) -> Numeric
52 FLOOR Floor of a number, i.e. the largest integer not greater than the input (Numeric) -> Numeric
53 INT Truncates a number by removing fractional parts (Numeric) -> Numeric
54 LOG10 Logarithm with base 10 (Numeric) -> Numeric
55 LOG Natural logarithm (base = Napier's constant) (Numeric) -> Numeric
56 LOG2 Logarithm with base 2 (Numeric) -> Numeric
57 RADIANS Computes the angle in radians given value in degrees. (Numeric) -> Numeric
58 ROUND Rounds the given input to the nearest integer. (Numeric) -> Numeric
59 SIGN Returns -1 for negative numbers and +1 for positive numbers (Numeric) -> Numeric
60 SIN Trigonometric sine (Numeric) -> Numeric
61 SINH Hyperbolic sine (Numeric) -> Numeric
62 SQRT Square root (Numeric) -> Numeric
63 TAN Trigonometric tangent (Numeric) -> Numeric
64 TANH Hyperbolic tangent (Numeric) -> Numeric
65 ATAN2 Angle (in radians) of a line segment in two dimensions with respect to the x-axis, with origin as one end point and the given inputs as the (y, x) coordinates. Returns a value in the range [0, 2 * pi). (Numeric, Numeric) -> Numeric
66 POW Raises the first value to the power of the second value. (Numeric, Numeric) -> Numeric
67 MOD Computes the remainder after dividing the first value by the second value. (Numeric, Numeric) -> Numeric
68 HYPOT Computes the hypotenuse length of a right-triangle the length of whose shorter sides are given. Equivalent to computing the magnitude of a vector in two dimensions. Together with ATAN2, this function can be used to convert two-dimensional cartesian coordinates into polar coordinates. (Numeric, Numeric) -> Numeric
69 JSON_ARRAY_LENGTH Computes the lengths of arrays that are JSON-serialized Null values and invalid strings in input result in -1. (Text) -> Numeric
(JSONArray) -> Numeric
70 JSON_ARRAY_NDISTINCT Computes the number of unique items in arrays that are JSON-serialized Null values and invalid strings in input result in -1. (Text) -> Numeric
(JSONArray) -> Numeric
71 JSON_ARRAY_SORT Given the input JSON-serialized array, this function sorts the contents of the array and outputs it as a new JSON-serialized array (Text) -> JSONArray
(JSONArray) -> JSONArray
72 JSON_ARRAY_UNIQUE Given the input JSON-serialized array, this function outputs a new JSON-serialized array with the elements of the input array, but without repetitions. Output will be sorted. (Text) -> JSONArray
(JSONArray) -> JSONArray
73 JSON_ARRAY_CONTAINS Returns whether the input JSON array contains the specified string as one of its elements. (JSONArray, Text) -> Boolean
(Text, Text) -> Boolean
(JSONArray, Numeric) -> Boolean
(Text, Numeric) -> Boolean
(JSONArray, Boolean) -> Boolean
(Text, Boolean) -> Boolean
74 JSON_ARRAY_OCCURRENCES Returns the number of times a specified string value occurs in a JSON array. Invalid strings and null values in input result in an output of 0. (JSONArray, Text) -> Numeric
(Text, Text) -> Numeric
(JSONArray, Numeric) -> Numeric
(Text, Numeric) -> Numeric
(JSONArray, Boolean) -> Numeric
(Text, Boolean) -> Numeric
75 JSON_ARRAY_INTERSECT Returns the elements common to first and second arrays, removing duplicates. Result will be sorted as strings. (JSONArray, JSONArray) -> JSONArray
76 JSON_ARRAY_UNION Returns the elements found in either the first or the second array. Duplicates will be removed and the result will be sorted as strings. (JSONArray, JSONArray) -> JSONArray
77 JSON_ARRAY_CONCAT Concatenates the first and second arrays, order is retained and duplicates are not removed. (JSONArray, JSONArray) -> JSONArray
78 JSON_OBJ_KEYS Gets the keys of the JSON object as a JSON array. (JSONObject) -> JSONArray