# Functions available for Custom Formulas

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

- Create a new column with a formula
- Transform columns with a formula
- Filter rows with a formula

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

**Arithmetic:**`+, -, /, *, %`

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

.**Conditional:**`==, !=, >, <, >=, <=`

. Note that a single`=`

is admitted in place of`==`

, as well as`<>`

in place of`!=`

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