Zoho DataPrep provides 315 built-in functions to transform, calculate, and derive new values from your data. Of these, 299 functions can be used with the Add formula transform to create formula columns, and the remaining 16 are window functions.
This page lists the 299 functions available in the Add formula transform, organized by category. Each function entry shows its syntax, the parameters it accepts, and an example of how it works. Use the contents below to jump to a category, then to a specific function.
To learn about the 16 window functions, see Window functions.
Returns the absolute value of the numeric value.
| Name |
Description |
|---|---|
|
num Decimal |
Parameter must be a numeric value from a column or an expression that returns a number. |
| Function |
Result |
|---|---|
| abs(-1) |
1 |
| abs(-5 + 2) |
3 |
| abs(-5.2) |
5.2 |
| abs('-5.2') |
5.2 |
| abs('-5 + 2') |
(returns an error) |
| abs('12/12/2025') |
(returns an error) |
Performs floating-point division. Returns a decimal value.
| Name |
Description |
|---|---|
|
numerator Decimal |
The parameter must be a non zero number, decimal, numeric column, or an expression or function returning a numeric value. |
|
denominator Decimal |
The parameter must be a non zero number, decimal, numeric column, or an expression or function returning a numeric value. |
| Function |
Result |
|---|---|
| div(3,2.1) |
1.5 |
| div(3,1.5) |
2 |
| div(3, 'a') |
(returns an error) |
| div(3,0) |
(returns an error) |
Returns param1+param2. Performs numeric addition.
| Name |
Description |
|---|---|
|
num1 Decimal |
Parameter must be a numeric value or a Number column. |
|
num2 Decimal |
Parameter must be a numeric value or a Number column. |
| Function |
Result |
|---|---|
| add('3', '2') |
5 |
Performs numeric subtraction. Returns param1 - param2.
| Name |
Description |
|---|---|
|
param1 Decimal |
Parameter must be a numeric value. |
|
param2 Decimal |
Parameter must be a numeric value. |
| Function |
Result |
|---|---|
| sub('3', '2') |
1 |
Performs numeric multiplication.
| Name |
Description |
|---|---|
|
param1 Decimal |
Parameter must be a numeric value. |
|
param2 Decimal |
Parameter must be a numeric value. |
| Function |
Result |
|---|---|
| mul('3', '2') |
6 |
Returns the binary representation of a number.
| Name |
Description |
|---|---|
|
param Number |
Parameter must be a numeric value or a numeric column or an expression that returns a number. |
| Function |
Result |
|---|---|
| bin('111') |
1101111 |
Returns the cube root of the number.
| Name |
Description |
|---|---|
|
param Decimal |
Parameter must be a number, or a numeric column or an expression that results in a numeric value. |
| Function |
Result |
|---|---|
| cbrt('27') |
3 |
Returns the smallest integer value that is bigger than or equal to a number.
| Name |
Description |
|---|---|
|
param Decimal |
Parameter must be a number, or a numeric column or an expression that results a numeric value. |
| Function |
Result |
|---|---|
| ceil('25.75') |
26 |
This function converts the number from 'from base' to 'to base'.
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a number or a number column, or an expression that returns a number. |
|
from_base Decimal |
The base value to be converted from. |
|
to_base Decimal |
The base value to be converted to. |
| Function |
Result |
|---|---|
| conv(100, 2, 10) |
4 |
| conv(100, 2) |
(returns an error) |
| conv(100, 2, 10, 20) |
(returns an error) |
| conv(100, 2, 's') |
(returns an error) |
Converts radians to degrees.
| Name |
Description |
|---|---|
|
param Decimal |
Specifies the angle in radians. |
| Function |
Result |
|---|---|
| degrees(3.141592653589793) |
180.0 |
Returns the exponential value of the given number. This value is the exponent (e) raised to the power specified as the parameter.
| Name |
Description |
|---|---|
|
param Decimal |
Parameter must be a number or an expression that returns a number. |
| Function |
Result |
|---|---|
| exp(1) |
2.718281828 |
Returns the largest integer that is less than or equal to the given parameter.
| Name |
Description |
|---|---|
|
param Decimal |
Parameter must be a number or an expression that returns a number. |
| Function |
Result |
|---|---|
| floor(-0.1) |
-1 |
Returns the largest value from a group of values.
| Name |
Description |
|---|---|
|
params Decimal |
Supports multiple parameters, The parameters must be a number column, or a list of numbers or an expression that returns a list of numbers. |
|
params Decimal |
Supports multiple parameters, The parameters must be a number column, or a list of numbers or an expression that returns a list of numbers. |
| Function |
Result |
|---|---|
| greatest(10, 9, 2, 4, 3) |
10 |
Returns the hexadecimal value of the given parameter.
| Name |
Description |
|---|---|
|
param Text |
Parameter can be any value or a column of Number, Text, or Boolean data type. |
| Function |
Result |
|---|---|
| hex(17) |
11 |
Returns the least value of all, skipping the null values.
| Name |
Description |
|---|---|
|
params Decimal |
Requires a minimum of two parameters which can be a number column, or a list of numbers, or an expression that returns a list of numbers. |
|
params Decimal |
Requires a minimum of two parameters which can be a number column, or a list of numbers, or an expression that returns a list of numbers. |
| Age |
|---|
| [20, 21, 24, 45, 46, 54, 60, 61, 63] |
| Weight |
|---|
| [123, 145, 160, 155, 200, 50, 134, 55, 60] |
| Function |
Result |
|---|---|
| least('Age', 'Weight') |
20, 21, 24, 45, 46, 50, 60, 55, 60 |
Returns the natural logarithm (base e) of the given parameter.
| Name |
Description |
|---|---|
|
param Decimal |
Parameter must be a number, a numeric column or an expression that returns a number. |
| Function |
Result |
|---|---|
| ln('1') |
0 |
Returns the logarithm of parameter given. This function also accepts custom base value for the logarithm.
| Name |
Description |
|---|---|
|
base Decimal |
Indicates the base of the logarithm function. Parameter must be a number, a numeric column or an expression that returns a number. |
|
param Decimal |
Parameter must be a number, a numeric column or an expression that returns a number. |
| Function |
Result |
|---|---|
| log(10,100) |
2 |
Returns the logarithm (base 10) of the parameter given.
| Name |
Description |
|---|---|
|
param Decimal |
Parameter must be a number, a numeric column or an expression that returns a number. |
| Function |
Result |
|---|---|
| log10('10.0') |
1 |
Returns the logarithm (base 2) of parameter given.
| Name |
Description |
|---|---|
|
param Decimal |
Parameter must be a number, a numeric column or an expression that returns a number. |
| Function |
Result |
|---|---|
| log2('2.0') |
1 |
Returns the remainder after performing numeric division on two numbers.
| Name |
Description |
|---|---|
|
param1 Decimal |
Parameter must be a numeric value which is the numerator used in the numeric division. |
|
param2 Decimal |
Parameter must be a numeric value which is the denominator used in the numeric division. |
| Function |
Result |
|---|---|
| mod('7','5') |
2 |
Returns the negated value of the number mentioned in param.
| Name |
Description |
|---|---|
|
param Decimal |
Specifies a number value that is to be negated. |
| Function |
Result |
|---|---|
| negative('1') |
-1 |
Returns the Pi value. The number π is a mathematical constant. It is defined as the ratio of a circle's circumference to its diameter.
| Function |
Result |
|---|---|
| pi() |
3.1415926536 |
Raises a number to the power that is specified by a number.
| Name |
Description |
|---|---|
|
param1 Decimal |
Specifies the number which is to be raised to a certain power. |
|
param2 Decimal |
Specifies the number which represents the power that the number is to be raised to. |
| Function |
Result |
|---|---|
| pow('2', '3') |
8 |
Converts angle in degrees to radian on the column.
| Name |
Description |
|---|---|
|
param Decimal |
angle in degrees |
| Function |
Result |
|---|---|
| radians(180) |
3.1415926536 |
Generates a random integer value, between the given numbers, in the column.
| Name |
Description |
|---|---|
|
num1 Decimal |
Specifies the starting number for the range from which a random number is returned. |
|
num2 Decimal |
Specifies the ending number for the range from which a random number is returned. |
| Function |
Result |
|---|---|
| random('0', '4') |
3 |
Returns a decimal number rounded to a specified number of decimal places using HALF_EVEN rounding mode.
| Name |
Description |
|---|---|
|
param Decimal |
Specifies the number column or the exact decimal number to be rounded off to the nearest value. |
|
d Number |
Specifies the number column or the exact number of decimal places that the number should be rounded off to. |
| Function |
Result |
|---|---|
| round_num('2.5', '0') |
2 |
| round_num('5.5', '0') |
6 |
| round_num('3.2', '0') |
3 |
Returns the square root of the values on the column.
| Name |
Description |
|---|---|
|
param Decimal |
Parameter must be a number or an expression that results in a numeric value or a Number column. |
| Function |
Result |
|---|---|
| sqrt('64') |
8 |
Returns the inverse cosine or arc cosine of the input expressed in radians
| Name |
Description |
|---|---|
|
num Decimal |
Parameter must be a numeric value, a numeric column or an expression that returns a number in the range of [-1...1] |
| Function |
Result |
|---|---|
| acos(0.34); |
1.22387943 |
Returns the arc sine or inverse sine of the input expressed in radians
| Name |
Description |
|---|---|
|
num Decimal |
Parameter must be a number, or a numeric column, or an expression that returns a number in the range of [-1...1] |
| Function |
Result |
|---|---|
| asin(0.34); |
19.876874 |
Returns the arc tangent or inverse tangent of the input expressed in radians
| Name |
Description |
|---|---|
|
num Decimal |
Parameter must be a number, or a numeric column, or an expression that returns a number in the range of [-1...1] |
| Function |
Result |
|---|---|
| atan(0.34); |
18.77803322 |
Returns arc tangent or inverse tangent for two numeric values
| Name |
Description |
|---|---|
|
num1 Decimal |
The parameter must be a number, or numeric columns, or expressions that returns a number |
|
num2 Decimal |
The parameter must be a number, or numeric columns, or expressions that returns a number |
| Function |
Result |
|---|---|
| atan2(5, 5); |
0.7853981633974483 |
Returns the trigonometric cosine of the given angle in radians.
| Name |
Description |
|---|---|
|
num Decimal |
Parameter must be a numeric value, a numeric column or an expression that returns a number |
| Function |
Result |
|---|---|
| cos(0); |
1.0 |
Returns the trigonometric sine of the given angle in radians.
| Name |
Description |
|---|---|
|
num Decimal |
Parameter must be a number, or a numeric column, or an expression that returns a number |
| Function |
Result |
|---|---|
| sin(0) |
0.0 |
Returns the trigonometric tangent of a given angle in radians.
| Name |
Description |
|---|---|
|
angle Decimal |
Parameter must be a number, or a numeric column, or an expression that returns a number |
| Function |
Result |
|---|---|
| tan(9.432) |
0.0072221648 |
Returns the trigonometric cotangent of a given angle in radians.
| Name |
Description |
|---|---|
|
num Decimal |
Parameter must be a numeric value, a numeric column, or an expression that returns a number |
| Function |
Result |
|---|---|
| cot(0.25) |
3.916317 |
Detects whether the value in the numeric column is positive, negative or zero
| Name |
Description |
|---|---|
|
number Decimal |
Specifies the number column for which the function is to be applied |
| Function |
Result |
|---|---|
| sign('-24') |
-1 |
Returns the quotient of the given values in integer.
| Name |
Description |
|---|---|
|
num1 Number |
Parameter must be a Number column or a number, or an expression that returns a number |
|
num2 Number |
Parameter must be a Number column or a number, or an expression that returns a number |
| Function |
Result |
|---|---|
| quotient('12', '4') |
3 |
Returns the Pearson coefficient of correlation between a set of number pairs.
| Name |
Description |
|---|---|
|
param1 Decimal |
First numeric expression. |
|
param2 Decimal |
Second numeric expression. |
| c1 |
|---|
| 3 |
| 3 |
| 6 |
| c2 |
|---|
| 2 |
| 3 |
| 4 |
| Function |
Result |
|---|---|
| corr(c1, c2) |
0.8660254037844387 |
Returns the population covariance of a set of number pairs.
| Name |
Description |
|---|---|
|
param1 Decimal |
First numeric expression. |
|
param2 Decimal |
Second numeric expression. |
| c1 |
|---|
| 1 |
| 2 |
| 3 |
| c2 |
|---|
| 1 |
| 2 |
| 3 |
| Function |
Result |
|---|---|
| covar_pop(c1, c2) |
0.6666666666666666 |
Returns the sample covariance of a set of number pairs.
| Name |
Description |
|---|---|
|
param1 Decimal |
First numeric expression. |
|
param2 Decimal |
Second numeric expression. |
| c1 |
|---|
| 1 |
| 2 |
| 3 |
| c2 |
|---|
| 1 |
| 2 |
| 3 |
| Function |
Result |
|---|---|
| covar_samp(c1, c2) |
1.0 |
Returns a cyclic redundancy check value of the expr as a bigint.
| Name |
Description |
|---|---|
|
expr Text |
Expression to compute the CRC32 checksum. Can be a string or binary value. |
| Function |
Result |
|---|---|
| crc32('Spark') |
1557323817 |
Returns Euler's number (e), which is approximately equal to 2.718281828459045.
| Function |
Result |
|---|---|
| e() |
2.718281828459045 |
Returns exp(expr) - 1. This function is useful for computing values close to zero with higher precision.
| Name |
Description |
|---|---|
|
expr Number |
Numeric expression for which exp(expr) - 1 will be calculated. |
| Function |
Result |
|---|---|
| expm1(0) |
0.0 |
Returns the factorial of expression. Valid input range is 0 to 20; otherwise returns null.
| Name |
Description |
|---|---|
|
param Decimal |
An integer expression between 0 and 20. Returns the factorial of expr, or null if out of range. |
| Function |
Result |
|---|---|
| factorial(5) |
120 |
| factorial(0) |
1 |
Returns the inverse hyperbolic cosine (area hyperbolic cosine) of the input expressed in radians
| Name |
Description |
|---|---|
|
num Decimal |
Parameter must be a numeric value, a numeric column, or an expression that returns a number greater than or equal to 1 |
| Function |
Result |
|---|---|
| acosh(1.5) |
0.96242365 |
Returns the inverse hyperbolic sine of the input expression.
| Name |
Description |
|---|---|
|
num Decimal |
A numeric expression or column |
| num |
|---|
| 0 |
| 1 |
| -1 |
| Function |
Result |
|---|---|
| asinh(0); |
0.0 |
| asinh(1); |
0.881373587 |
| asinh(-1); |
-0.881373587 |
Returns the inverse hyperbolic tangent (atanh) of the input expression. The input must be in the range (-1, 1).
| Name |
Description |
|---|---|
|
num Decimal |
A numeric expression or column, must be between -1 and 1 |
| num |
|---|
| 0 |
| 0.5 |
| -0.5 |
| Function |
Result |
|---|---|
| atanh(0); |
0.0 |
| atanh(0.5); |
0.549306144 |
| atanh(-0.5); |
-0.549306144 |
Returns the numeric expression rounded to d decimal places using HALF_UP rounding mode.
| Name |
Description |
|---|---|
|
num Decimal |
Numeric expression to be rounded. |
|
d Decimal |
[Optional] Number of decimal places to round to. |
| Function |
Result |
|---|---|
| round(2.5, 0) |
3 |
Returns the average value in a numeric column or list
| Name |
Description |
|---|---|
|
list Decimal |
Parameter must be a numeric column, or a list of numbers |
| ticket_count |
|---|
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
| 80 |
| 90 |
| Function |
Result |
|---|---|
| avg(ticket_count); |
60 |
Returns the minimum value in a list of numeric values
| Name |
Description |
|---|---|
|
num Decimal |
Parameter must be a numeric column or a list of numeric values |
| ticket_count |
|---|
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
| 80 |
| 90 |
| Function |
Result |
|---|---|
| min('ticket_count'); |
30 |
Returns the maximum value from a numeric column
| Name |
Description |
|---|---|
|
num Decimal |
Parameter must be a numeric column |
| ticket_count |
|---|
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
| 80 |
| 90 |
| Function |
Result |
|---|---|
| max('ticket_count'); |
90 |
Returns the maximum date value from a Date or a Datetime column.
| Name |
Description |
|---|---|
|
date Datetime |
Parameter must be a Date column or a Datetime column. |
| Data column |
|---|
| 06 Dec 2018 |
| 17 Nov 2019 |
| 20 Oct 2020 |
| Function |
Result |
|---|---|
| max_date(Data column) |
20 Oct 2020 |
Returns the minimum date value from a Date or a Datetime column.
| Name |
Description |
|---|---|
|
date Datetime |
Parameter must be a Date column or a Datetime column. |
| Data column |
|---|
| 06 Dec 2018 |
| 17 Nov 2019 |
| 20 Oct 2020 |
| Function |
Result |
|---|---|
| min_date(Data column) |
06 Dec 2018 |
Returns the sum of the values in the given column
| Name |
Description |
|---|---|
|
num Decimal |
Parameter must be a numeric column, or a list of numbers |
| marks |
|---|
| 45 |
| 33 |
| 56 |
| 70 |
| Function |
Result |
|---|---|
| sum(marks) |
205 |
Returns the most occurring value in the column
| Name |
Description |
|---|---|
|
num Decimal |
Parameter must be a numeric column |
| ticket_count |
|---|
| 30 |
| 40 |
| 30 |
| 60 |
| 30 |
| 80 |
| 90 |
| Function |
Result |
|---|---|
| mode('ticket_count'); |
30 |
Returns the total number of values in the column. Null values (empty cells) are ignored.
| Name |
Description |
|---|---|
|
col Text |
Parameter must be a column |
| ticket_count |
|---|
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
| 80 |
| 90 |
| Function |
Result |
|---|---|
| count(ticket_count); |
7 |
Returns the total number of values in the column including null values (empty cells).
| Name |
Description |
|---|---|
|
col Text |
Parameter must be a column |
| ticket_count |
|---|
| 30 |
| 40 |
| 50 |
| 70 |
| 80 |
| 90 |
| Function |
Result |
|---|---|
| count_wb(ticket_count); |
7 |
Returns the variance calculated from values of a group in a number format.
| Name |
Description |
|---|---|
|
param Decimal |
Parameter must be a numeric group of values such as a Number column. |
| ticket_count |
|---|
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
| 80 |
| 90 |
| Function |
Result |
|---|---|
| variance(ticket_count) |
466.66 |
Returns the standard deviation calculated from the input values.
| Name |
Description |
|---|---|
|
param Decimal |
Parameter must be a numeric value or a Number column. |
| Score |
|---|
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
| 60 |
| Function |
Result |
|---|---|
| stddev(Score) |
18.70 |
Collects the distinct values from a column as a list.
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a column conforming to any one data type. |
| Column1 |
|---|
| "Australia" |
| "United States" |
| "Australia" |
| "Canada" |
| "Canada" |
| Function |
Result |
|---|---|
| collect_set(Column1) |
["Australia","United States","Canada"] |
Returns the min value on satisfying the given condition
| Name |
Description |
|---|---|
|
num Decimal |
Parameter must be a Number column |
|
condition Boolean |
Condition must return a Boolean value based on which the function is calculated |
| ticket_count |
|---|
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
| 80 |
| 90 |
| Function |
Result |
|---|---|
| min_if(ticket_count, ticket_count!=30) |
40 |
Returns the max value on satisfying the given condition
| Name |
Description |
|---|---|
|
num Decimal |
Parameter must be a Number column |
|
condition Boolean |
Condition must return a Boolean value based on which the function is calculated |
| ticket_count |
|---|
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
| 80 |
| 90 |
| Function |
Result |
|---|---|
| max_if(ticket_count, ticket_count!=90) |
80 |
Returns the average value on satisfying the given condition
| Name |
Description |
|---|---|
|
num Decimal |
Parameter must be a Number column |
|
condition Boolean |
Condition must return a Boolean value based on which the function is calculated |
| ticket_count |
|---|
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
| 80 |
| 90 |
| Function |
Result |
|---|---|
| avg_if(ticket_count, ticket_count!=30) |
65 |
Returns the sum of the values on satisfying the given condition
| Name |
Description |
|---|---|
|
num Decimal |
Parameter is mandatory and must be a numeric column or value |
|
condition Boolean |
Condition must return a Boolean value based on which the function is calculated |
| marks |
|---|
| [90, 85, 95, 65] |
| grade |
|---|
| [A, B, A, D] |
| Function |
Result |
|---|---|
| sum_if(marks, `grade`=='A') |
[185, 185, 185, 185] |
Returns the mode value on satisfying the given condition
| Name |
Description |
|---|---|
|
num Decimal |
Parameter must be a Number column |
|
condition Boolean |
Condition must return a Boolean value based on which the function is calculated |
| ticket_count |
|---|
| 30 |
| 40 |
| 30 |
| 40 |
| 30 |
| 80 |
| 90 |
| Function |
Result |
|---|---|
| mode_if(ticket_count, ticket_count!=30) |
40 |
Returns the population standard deviation of the given column after applying the specified condition.
| Name |
Description |
|---|---|
|
number Decimal |
Parameter must be a numeric column's value or an expression that returns a number. |
|
condition Boolean |
Condition must return a boolean value based on which the function is calculated. |
| Country |
|---|
| India |
| USA |
| Germany |
| Movie |
|---|
| Avatar |
| Inception |
| Avatar |
| Rating |
|---|
| 9.432 |
| 1.0 |
| 2.0 |
| Function |
Result |
|---|---|
| stddev_if(`Rating`, `Movie`='Avatar') |
5.2552175978 |
Returns the variance of the values on satisfying the given condition
| Name |
Description |
|---|---|
|
num Decimal |
Parameter is mandatory and must be a numeric column or an expression that returns a list of numbers |
|
condition Boolean |
Condition must return a Boolean value based on which the function is calculated |
| sales_amount |
|---|
| 9.432 |
| 1 |
| 2 |
| country |
|---|
| India |
| USA |
| India |
| Function |
Result |
|---|---|
| variance_if(sales_amount, 'country'=='India'); |
27.6173124, 27.617312, 27.617312 |
Returns the total number of values on satisfying the given condition
| Name |
Description |
|---|---|
|
col Text |
Parameter must be a column |
|
condition Boolean |
Condition must return a Boolean value based on which the function is calculated |
| ticket_count |
|---|
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
| 80 |
| 90 |
| Function |
Result |
|---|---|
| count_if(ticket_count, ticket_count!=30) |
6 |
Returns the total number of distinct values in the column
| Name |
Description |
|---|---|
|
col Text |
Parameter must be a column |
| ticket_count |
|---|
| 30 |
| 40 |
| 30 |
| 60 |
| 70 |
| 60 |
| 90 |
| Function |
Result |
|---|---|
| count_distinct(ticket_count); |
5 |
Returns the total number of distinct values satisfying the given condition
| Name |
Description |
|---|---|
|
col Text |
Parameter must be a column |
|
condition Boolean |
Condition must return a Boolean value based on which the function is calculated |
| ticket_count |
|---|
| 30 |
| 40 |
| 50 |
| 60 |
| 30 |
| 80 |
| 40 |
| Function |
Result |
|---|---|
| count_distinct_if(ticket_count, ticket_count!=30) |
4 |
Returns the exact percentile value of 'num' at the given 'percentage'
| Name |
Description |
|---|---|
|
num Decimal |
Parameter must be a Number column |
|
percentage Text |
Parameter must be a Number column or a number, or an expression that returns a value between 0.0 to 1.0 |
|
frequency Number |
[Optional] Parameter must be a Number column or a number, or an expression that returns a positive number |
| ticket_count |
|---|
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
| 80 |
| 90 |
| Function |
Result |
|---|---|
| percentile(ticket_count, 0.3, 0) |
3.0 |
Returns the Pearson Coefficient of correlation between two numerical columns
| Name |
Description |
|---|---|
|
numList1 Decimal |
parameter must be a number column |
|
numList2 Decimal |
parameter must be a number column |
| Age |
|---|
| [20, 21, 24, 45, 46, 54, 60, 61, 63] |
| Weight |
|---|
| [123, 145, 160, 155, 200, 150, 134, 133, 120] |
| Function |
Result |
|---|---|
| correl('Age', 'Weight'); |
-0.1316 |
Collects all the values in the column as a list.
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a column conforming to any one data type. |
| Column |
|---|
| 30 |
| 50 |
| 60 |
| Function |
Result |
|---|---|
| collect_list(Column) |
[30, 50, 60], [30, 50, 60], [30, 50, 60] |
Returns true if at least one value of `expr` is true.
| Name |
Description |
|---|---|
|
params Boolean |
Boolean expression or column whose values are evaluated. The function returns true if at least one value is true. |
| Function |
Result |
|---|---|
| any(true,false,false) |
true |
| any(NULL,true,false) |
true |
| any(false,false,NULL) |
false |
Returns the estimated number of distinct values using the HyperLogLog++ algorithm. The optional `relativeSD` parameter defines the maximum relative standard deviation allowed.
| Name |
Description |
|---|---|
|
expr Decimal |
The column or expression whose distinct values are to be approximated. |
|
relativeSD Decimal |
[Optional] Defines the maximum relative standard deviation allowed for the estimation. |
| OrderID |
|---|
| 1023 |
| 1023 |
| 1050 |
| 1088 |
| 1088 |
| 1101 |
| Function |
Result |
|---|---|
| approx_count_distinct(OrderID) |
4 |
Returns the bitwise XOR of all non-null input values, or null if none.
| Name |
Description |
|---|---|
|
expr Number |
The numeric expression or column for which the bitwise XOR is calculated. Only non-null values are considered. |
| col |
|---|
| 3 |
| 5 |
| Function |
Result |
|---|---|
| bit_xor(col); |
6 |
Returns the ASCII character having the binary equivalent to the parameter. If n is larger than 256 the result is equivalent to char(n % 256).
| Name |
Description |
|---|---|
|
param Number |
Accepts multiple parameters. Each parameter must be a number, a numeric column, or an expression that returns a number. |
| Function |
Result |
|---|---|
| char('65') |
A |
| char('65', '66', '67') |
ABC |
| char('33', '289') |
!! |
| char('33', '64', '60', '80', '100') |
!@<Pd |
| char('33', '64', 60, 80, 100) |
!@<Pd |
| char('sgr', 60, 80, '100') |
(returns an error) |
| char('d', 'a', 't', 'a') |
(returns an error) |
Returns the concatenated text of the given parameters. Returns null, if any one of the parameters is null.
| Name |
Description |
|---|---|
|
params Text |
Parameters must be a Text or a Text column, or an expression that returns a Text. |
|
params Text |
Parameters must be a Text or a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| concat("NOW", "HERE") |
NOWHERE |
Returns the concatenated text of the given parameters along with the separator. Returns null, if any one of the parameter is null.
| Name |
Description |
|---|---|
|
separator Text |
The first of the params is considered as the separator, using which the rest of the consecutive arguments will be concatenated. |
|
param1 Text |
The first string to be concatenated. |
|
param2 Text |
One or more additional strings to concatenate with the first string using the specified separator. |
| Function |
Result |
|---|---|
| concat_ws('_', 'NOW', 'HERE') |
NOW_HERE |
| concat_ws('_', 'NOW', 'HERE', 'IT', 'IS') |
NOW_HERE_IT_IS |
| concat_ws('_', 'NOW') |
(returns an error) |
Returns the decoded value of the first argument using the second argument character set (UTF-8, ASCII, UTF-10, etc.).
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a Text, a Text column, or an expression that returns a Text. |
|
charset Text |
Specifies the character set such as UTF-8, ASCII, UTF-10, etc. |
| Function |
Result |
|---|---|
| decode("ABC", "UTF-8") |
ABC |
Returns the encoded value of the first argument using the second argument character set (utf-8, ascii, utf-10, etc.).
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a Text, a Text column, or an expression that returns a Text. |
|
charset Text |
Specifies the character set. 'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16' are the supported character sets. |
| Function |
Result |
|---|---|
| encode("ABC", "UTF-8") |
ABC |
Returns the text with the first letter of each word in uppercase. All other letters are in lowercase. Words are delimited by white space.
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| initcap('zoho one is an operating system for your business') |
Zoho One Is An Operating System For Your Business |
Returns the leftmost 'length' of characters from the first parameter, if the length is less than or equal to 0 the result is an empty text.
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a Text, a Text column, or an expression that returns a Text. |
|
length Number |
length must be a number or a column of Number type. |
| Function |
Result |
|---|---|
| left("NOWHERE", 3) |
NOW |
The LOWER function returns the given text with lower case for each character.
| Name |
Description |
|---|---|
|
param Text |
Specifies the Text that needs to be converted with lower case for each character. The parameter can be a text value, a text column or an expression that results in a text value. |
| Function |
Result |
|---|---|
| lower("Zoho, Your Life's Work, Powered By Our Life's Work!") |
zoho, your life's work, powered by our life's work! |
Removes the leading white space characters from the text.
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a Text, a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| ltrim(" Zoho") |
Zoho |
Extracts a group that matches regex expression.
| Name |
Description |
|---|---|
|
str Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
|
regexp Text |
Parameter must be a regular expression pattern passed as a Text. |
|
groupIdx Number |
[Optional] Paramter must be an integer which indicates the index of the group that needs to be extracted. |
| Function |
Result |
|---|---|
| regexp_extract("100abc", "\\d+" , 0) |
100 |
| regexp_extract( "100-abc" , "(\\d+)-([a-zA-Z]+)" , 2) |
abc |
Replaces all the subtexts of a text 'str' that matches the regex with the text mentioned in 'replace_with'.
| Name |
Description |
|---|---|
|
str Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
|
regex Text |
Parameter must be a Text specifying the regular expression. |
|
replace_with Text |
Parameter must be a Text specifying the regular expression. |
| Function |
Result |
|---|---|
| regexp_replace("100-200", "(\\d+)", 'num') |
num-num |
Replaces all occurrences of search with the provided replacement text.
| Name |
Description |
|---|---|
|
str Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
|
search Text |
A Text expression. If search is not found in str, str is returned unchanged. |
|
replace Text |
A Text expression. If replace is not specified or is an empty text, nothing replaces the text that is removed from str. |
| Function |
Result |
|---|---|
| replace('ABCabc', 'abc', 'DEF') |
ABCDEF |
Returns the reversed version of the given text.
| Name |
Description |
|---|---|
|
str Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| reverse('Zoho One') |
enO ohoZ |
Returns the rightmost length `len` of characters from the text `str`. If the length is less than or equal to 0 the result is an empty text.
| Name |
Description |
|---|---|
|
str Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
|
len Number |
Specifies the number of characters from the right of the Text to be returned. |
| Function |
Result |
|---|---|
| right('Zoho One', '3') |
One |
Removes the trailing space characters from the provided text value.
| Name |
Description |
|---|---|
|
str Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| rtrim('Zoho One ') |
Zoho One |
Returns the Soundex code of the text values in the column.
| Name |
Description |
|---|---|
|
str Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| soundex('Miller') |
M460 |
Returns the subtext of the sentence text that starts at a position index and is of a given length.
| Name |
Description |
|---|---|
|
sentence Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
|
pos Number |
Parameter must be a number representing the index of the starting position of the text to be extracted. Position indexing starts from 1. |
|
len Number |
[Optional] Parameter must be a number representing the length of the text to be extracted from the 'sentence'. |
| Function |
Result |
|---|---|
| substr('Zoho One', '4', '1') |
o |
Removes the leading and trailing space characters from the text values in the column.
| Name |
Description |
|---|---|
|
param Text |
Parameter can be a column of any datatype. |
| Function |
Result |
|---|---|
| trim(' Zoho One ') |
Zoho One |
Returns a text with all of the characters changed to uppercase.
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| upper('Zoho One') |
ZOHO ONE |
Returns the text with all case inverted i.e. upper case is converted to lower case and vice versa.
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| invert_case('Zoho One is an Operating System for your Business') |
zOHO oNE IS AN oPERATING sYSTEM FOR YOUR bUSINESS |
Returns a text with alternate lower and upper case for each character of the input text starting with lower case for the first character.
| Name |
Description |
|---|---|
|
string Text |
Specifies the Text that needs to be converted with alternate case for each character. |
| Function |
Result |
|---|---|
| alternate_case("Zoho, Your Life's Work, Powered By Our Life's Work!") |
zOhO, yOuR LiFe's wOrK, pOwErEd bY OuR LiFe's wOrK! |
Returns a text of the values in the columns after applying the sentence case to it.
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| sentence_case('welcome to the united states of america') |
"Welcome to the united states of america" |
Returns the text after applying the camel case to it.
| Name |
Description |
|---|---|
|
string Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| camel_case("Zoho one is an operating system for your business") |
ZohoOneIsAnOperatingSystemForYourBusiness |
Returns a text of the values in the columns after applying the title case to it. Title case means that the first letter of each word is capitalized, except for certain small words, such as articles and short prepositions
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| title_case('united states of america') |
United States of America |
Returns a text value where the new lines are converted to spaces.
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a Text, a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| lines_to_space('Zoho\nOne') |
Zoho One |
Returns a text value where the spaces are converted to new lines.
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a Text, a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| space_to_lines('Zoho One') |
Zoho\nOne |
Returns a text value where the tabs are converted to spaces.
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| tab_to_space('Zoho\tOne') |
Zoho One |
Returns a text value where the spaces are converted to tabs.
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| space_to_tab('Zoho One') |
Zoho\tOne |
Returns a text with multiple spaces into a single space. The \s given in the example is only a representation of the whitespace character. The input data should contain actual space.
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| to_single_space('Zoho\s\s\sOffice\s\sSuite') |
Zoho Office Suite |
Returns a text with multiple tab spaces into a sinlge tab space.
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| to_single_tab('Zoho\t\t\tOffice\tSuiteSQL\tOrg') |
Zoho\tOffice\tSuiteSQL\tOrg |
Removes all the empty lines in the column
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a Text, a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| remove_empty_lines('Zoho\n\n\nOffice\n\nSuite') |
Zoho\nOffice\nSuite |
Truncates a given number of characters from the input text value.
| Name |
Description |
|---|---|
|
param1 Text |
Parameter can be a column of any datatype. |
|
param2 Number |
Specifies the number of characters to be removed from the Text. The value of the parameter should be smaller than the length of the Text. |
| Function |
Result |
|---|---|
| truncate_char(Zoho One, 4) |
Zoho |
Truncates a given number of words from the input text value.
| Name |
Description |
|---|---|
|
param1 Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
|
param2 Number |
Specifies the number of words to be removed from the Text. The value of the parameter should be smaller than the number of words in the Text and greater than 0. |
| Function |
Result |
|---|---|
| truncate_words('Zoho One', 1) |
Zoho |
Removes all the spaces in the column.
| Name |
Description |
|---|---|
|
str Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| remove_space(' Zoho One ') |
"ZohoOne" |
Splits the text around occurrences that match the regular expression.
| Name |
Description |
|---|---|
|
str Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
|
regex Text |
Parameter must be a Text mentioning the regular expression that returns a Text. |
| Function |
Result |
|---|---|
| split('oneAtwoBthreeC', '[ABC]') |
["one","two","three",""] |
Returns true if the text string1 contains string2
| Name |
Description |
|---|---|
|
string1 Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
|
string2 Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
|
num Number |
Parameter must be a numeric value. Using '0' will make the search case-sensitive in finding the Text. Using any other number will render the search case-insensitive. |
| Function |
Result |
|---|---|
| contains("Pineapple", "Apple", 0) |
True |
Compares two text columns and returns true if the text string1 starts with string2.
| Name |
Description |
|---|---|
|
string1 Text |
Specifies Text values or a Text column. |
|
string2 Text |
Specifies Text values or a Text column |
|
number Number |
Specifies if the comparison should be case-sensitive (input '1') or non case-sensitive (input '0'). |
| Function |
Result |
|---|---|
| starts_with('Sunday', 'Sun', '1') |
True |
| starts_with('Sunday', 'Mon', '0') |
False |
Returns true if the text string1 ends with string2.
| Name |
Description |
|---|---|
|
string1 Text |
Parameter must be a Text, a Text column, or an expression that returns a Text. |
|
string2 Text |
Parameter must be a Text, a Text column, or an expression that returns a Text. |
|
num Number |
Parameter must be a numeric value. Using '0' will make the search case-sensitive in finding the sting. Using any other number will render the search case-insensitive. |
| Function |
Result |
|---|---|
| ends_with("Newyork","york", 0) |
True |
Returns the keywords present in the column.
| Name |
Description |
|---|---|
|
string Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
|
maxKeywordLength Number |
Maximum number of words in a single keyword set. For example, for the sentence, 'Operating Systems' key length can be at most two. |
|
topElements Number |
Pick top N elements from results. |
|
model Text |
Three models are supported. They are namely, ONLY_VALID_KEYWORDS, RANKED_KEYWORDS, RANKED_KEYWORDS_WITH_INDEX. |
| Function |
Result |
|---|---|
| keyword_extraction(`A room without books is like a body without a soul.` , '1' , '4' , 'ONLY_VALID_KEYWORDS') |
('room','books','body','soul') |
Detect language from the given text. Supports 71 languages.
| Name |
Description |
|---|---|
|
string Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
|
langType Text |
Parameter must be either be 'code', or 'name'. Code will display the language code as a result, whereas name will display the name of the language. |
| Function |
Result |
|---|---|
| lang_predict('மதிப்புரைகளைப் படிக்கவும்' , 'name') |
Tamil |
| lang_predict('மதிப்புரைகளைப் படிக்கவும' , 'code') |
ta |
Returns the position of the first occurrence of the text 'string1' in 'string2' after the 'position' given
| Name |
Description |
|---|---|
|
string1 Text |
Parameter must be a Text, a Text column or an expression that returns a Text |
|
string2 Text |
Parameter must be a Text, a Text column or an expression that returns a Text |
|
position Number |
[Optional] Specifies the position after which 'string2' should be located. |
| Function |
Result |
|---|---|
| locate('bar', 'foobarbar') |
4 |
| locate('bar', 'foobarbar' , 5) |
5 |
Returns the 'string' text left-padded with a 'padString' for the 'length' specified. Padding will be applied only when 'length' value is more than the length of the text, if not the output text is shortened to the 'length' parameter.
| Name |
Description |
|---|---|
|
string Text |
Parameter must be a Text, a Text column or an expression that returns a Text |
|
length Number |
Specifies the total length of the output Text |
|
string2 Text |
Specifies the Text that must be added to, or replaced in the 'Text1' value |
| Function |
Result |
|---|---|
| lpad('abcd', 3, 'A') |
Aab |
| lpad('abcd', 7, 'A') |
AAAabcd |
Returns the 'string' text right-padded with a 'padString' for the 'length' specified. Padding will be applied only when 'length' value is more than the length of the text, if not the output text is shortened to the 'length' parameter.
| Name |
Description |
|---|---|
|
string Text |
Parameter must be a Text column or an expression that returns a Text |
|
length Number |
Specifies the total length of the output Text |
|
string2 Text |
Specifies the Text that must be added to, or replaced in the 'Text1' value |
| Function |
Result |
|---|---|
| rpad('abcd', 5, 'A') |
abcdA |
| rpad('abcd', 7, 'A') |
abcdAAA |
Returns the text value available at the index number specified in the first argument
| Name |
Description |
|---|---|
|
num Number |
Parameter must be a numerical value, a numerical column or an expression that returns a number |
|
string Text |
Accepts multiple parameters. All parameters must be a Text column or an expression that returns a Text. |
| Function |
Result |
|---|---|
| elt('5', 'Zoho', 'is the', 'operating system', 'for your', 'business') |
business |
| elt('6', 'Zoho', 'is the', 'operating system', 'for your', 'business') |
(returns an error) |
| elt(3, 1,2,3) |
3 |
Searches the value in the first argument with all the rest of the arguments and returns the position of the argument where the first match is found
| Name |
Description |
|---|---|
|
string1 Text |
First parameter must be a Text, a Text column or an expression that returns a Text |
|
string2 Text |
Accepts multiple parameters. Each parameter from the second must be a Text, a Text column or an expression that returns a Text. |
| Function |
Result |
|---|---|
| field('as','has','as','have') |
2 |
Inserts the text string2 in string1 at the index 'position' for the specified 'length'
| Name |
Description |
|---|---|
|
string1 Text |
Parameter must be a Text, a Text column or an expression that returns a Text |
|
position Number |
The position where the 'Text2' parameter must be inserted |
|
length Number |
The length of the Text |
|
string2 Text |
Specifies the Text that must be added to, or replaced in the 'Text1' value |
| Function |
Result |
|---|---|
| insert('abcddb', 3, 2, 'efgh') |
abefghdb |
Repeats the given 'string' text for the specified number of times and returns the constructed text.
| Name |
Description |
|---|---|
|
string Text |
Parameter must be a Text column or a Text, or an expression that returns a Text |
|
num Number |
Parameter must be a numerical value, a numerical column or an expression that returns a number. Value must less than or equal to 9999. |
| Function |
Result |
|---|---|
| repeat('Abcd', 3) |
AbcdAbcdAbcd |
Returns the ASCII code of the first character of the input string. Only the first character is evaluated, regardless of whether the input begins with a letter or a number.
| Name |
Description |
|---|---|
|
string Text |
Parameters can be a Text, Number, Date, or any column. |
| Function |
Result |
|---|---|
| ascii('A') |
65 |
| ascii('222') |
50 |
| ascii('2') |
50 |
Returns the value of the length of the text parameter as bit length
| Name |
Description |
|---|---|
|
string Text |
Parameters must be a Text, or a Text column, or an expression that returns a Text |
| Function |
Result |
|---|---|
| bit_length("AA") |
16 |
Returns the index of the first occurrence of the text 'string2' in 'string1'
| Name |
Description |
|---|---|
|
string1 Text |
The parameter must be a Text, a Text column, or an expression that returns a Text |
|
string2 Text |
The parameter must be a Text, a Text column, or an expression that returns a Text |
| Function |
Result |
|---|---|
| index_of('abcddb','db') |
5 |
Returns a text consisting of n spaces.
| Name |
Description |
|---|---|
|
number Number |
Specifies number of spaces required. Parameter can be a number column specifying the number of spaces. |
| Function |
Result |
|---|---|
| space('2') |
' ' |
Returns the corresponding character for each pair of hexadecimal digits
| Name |
Description |
|---|---|
|
HexVal Text |
Parameter must be a hexadecimal Text value |
| Function |
Result |
|---|---|
| unhex('476F64'); |
God |
Compares two text columns and returns 0 if the columns are of equal size, 1 if column 1 is bigger than column 2 and returns -1 if column 2 is bigger than column 1.
| Name |
Description |
|---|---|
|
string1 Text |
Specifies Text values or a Text column |
|
string2 Text |
Specifies Text values or a Text column |
| Function |
Result |
|---|---|
| string_compare('Sunday', 'Monday') |
0 |
Removes all characters from a text that are not letters, numbers, accented Latin characters, non-Latin characters, or whitespace.
| Name |
Description |
|---|---|
|
string Text |
Parameter must be a Text column or a Text, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| remove_symbols('Abcd$%```') |
Abcd |
Returns the characters between two given texts from the main text.
| Name |
Description |
|---|---|
|
fulltext Text |
All parameters must be of Text or a Text column, or an expression that returns a Text |
|
start_text Text |
Specifies the Text value from which the sub text should be obtained |
|
end_text Text |
Specifies the Text value to which the sub text should be obtained |
|
pos Number |
[Optional] It is an optional positive numeric value which specifies the position of the matching delimiter from which the sub text should be obtained |
| Function |
Result |
|---|---|
| substring_between('Sunday', 'n', 'y', 1) |
da |
Returns the count of occurrence of given sub text in the given text
| Name |
Description |
|---|---|
|
fulltext Text |
The parameter must be of Text or a Text column, or an expression that returns a Text |
|
sub_text Text |
Specifies the sub text whose count of occurrences is to be obtained |
| Function |
Result |
|---|---|
| substring_count('Sunday', 'day') |
1 |
Returns the sub text before the delimiter, based on the count of occurrences of the delimiter in the given text column
| Name |
Description |
|---|---|
|
fulltext Text |
All parameters must be of Text or a Text column, or an expression that returns a Text |
|
delimiter Text |
Specifies a delimiter based on which the sub text is to be obtained from the given value |
|
count Number |
It is a numeric value which specifies the number of delimiters before which the sub text should be obtained |
| Function |
Result |
|---|---|
| substring_index('how.are.you', '.' , 1) |
how |
Returns a text with multiple lines into a single line.
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a Text, or a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| to_single_line('Zoho\n\n\nOne\n\n') |
Zoho One |
Returns the character length of string data or number of bytes of binary data. The length of string data includes trailing spaces. The length of binary data includes binary zeros.
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a string expression. |
| Function |
Result |
|---|---|
| char_length('Spark SQL ') |
10 |
| char_length(x'537061726b2053514c') |
9 |
| CHAR_LENGTH('Spark SQL ') |
10 |
| CHARACTER_LENGTH('Spark SQL ') |
10 |
Returns the character length of string data or number of bytes of binary data. The length of string data includes trailing spaces. The length of binary data includes binary zeros.
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a string expression. |
| Function |
Result |
|---|---|
| char_length('Spark SQL ') |
10 |
| char_length(x'537061726b2053514c') |
9 |
| CHAR_LENGTH('Spark SQL ') |
10 |
| CHARACTER_LENGTH('Spark SQL ') |
10 |
Returns the ASCII character having the binary equivalent to the given number. If the number is larger than 256, the result is equivalent to chr(n % 256).
| Name |
Description |
|---|---|
|
num Number |
Numeric expression representing the ASCII code. |
| Function |
Result |
|---|---|
| chr(65) |
A |
Returns the 1-based index of the given string in the comma-delimited list. Returns 0 if the string was not found or contains a comma.
| Name |
Description |
|---|---|
|
str Text |
The string to search for. Returns 0 if the string contains a comma. |
|
str_array Text |
A comma-delimited list of strings to search within. |
| Function |
Result |
|---|---|
| find_in_set('ab', 'abc,b,ab,c,def') |
3 |
| find_in_set('x', 'a,b,c') |
0 |
Extracts a JSON object from the specified JSON string using the given JSON path expression.
| Name |
Description |
|---|---|
|
json_txt Text |
A valid JSON string from which to extract data. |
|
path Text |
A JSON path expression specifying the object to extract. |
| Function |
Result |
|---|---|
| get_json_object('{"a":"b"}', '$.a') |
b |
Encodes the input string or binary data into a Base64-encoded string.
| Name |
Description |
|---|---|
|
input Text |
The input string or binary to be encoded in Base64 format |
| Function |
Result |
|---|---|
| base64('Spark SQL'); |
U3BhcmsgU1FM |
| base64(x'537061726b2053514c'); |
U3BhcmsgU1FM |
Returns the current date as a date value. Function accepts no parameters.
| Function |
Result |
|---|---|
| current_date() |
09/21/2019 |
Returns the current timestamp as a Datetime value. Function accepts no parameters.
| Function |
Result |
|---|---|
| current_timestamp() |
09/19/2019 19:18:55 |
Converts the Datetimestamp to the format specified in the parameter.
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column to change the format. |
|
format Text |
Specifies the format in which the date should be returned by the function. |
| Data column |
|---|
| 2017-06-15 |
| Function |
Result |
|---|---|
| date_format(Data column, "dd MMM, yyyy") |
15 June, 2017 |
Returns the day of month of the date.
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column. |
| Data column |
|---|
| 2009-03-24 |
| Function |
Result |
|---|---|
| day_of_month(Data column) |
24 |
Returns the day of the week for the given date. [1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday]
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column. |
| Data column |
|---|
| 06-12-2018 |
| Function |
Result |
|---|---|
| day_of_week(Data column) |
5 |
Returns the day of the week from the given date
| Name |
Description |
|---|---|
|
date Datetime |
Parameter must be a Date column or a Datetime column |
| Data column |
|---|
| 06 Dec 2018 |
| Function |
Result |
|---|---|
| weekday(Data column); |
3 |
Returns the day of the week (as name) for the given date.
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column. |
| Data column |
|---|
| 06 Dec 2018 |
| Function |
Result |
|---|---|
| day_of_week_name(Data column) |
Thursday |
Returns the day of year (in number) of the date/timestamp.
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column. |
| Data column |
|---|
| 25-03-2009 |
| Function |
Result |
|---|---|
| day_of_year(Data column) |
84 |
Returns the hour component of the timestamp.
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies a Datetime column. |
| Data column |
|---|
| 2009-03-07 20:18:34:543 |
| Function |
Result |
|---|---|
| hour(Data column) |
20 |
Returns the last day of the month mentioned in the given date.
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies a Date column or a Datetime column. |
| Data column |
|---|
| 2016-04-09 |
| Function |
Result |
|---|---|
| last_day(Data column) |
2016-04-30 |
Returns the minute component of the timestamp.
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies a Datetime column. |
| Data column |
|---|
| 2009-07-30 12:58:59 |
| Function |
Result |
|---|---|
| minute(Data column) |
58 |
Returns the minute component of the day from the timestamp.
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies a Datetime column. |
| Data column |
|---|
| 2009-07-30 12 58 59 |
| Function |
Result |
|---|---|
| minute_of_day(Data column) |
778 |
Returns the minute component of an hour from the timestamp.
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies a Datetime column. |
| Data column |
|---|
| 2009-07-30 12 58 59 |
| Function |
Result |
|---|---|
| minute_of_hour(Data column) |
58 |
Returns the millisecond of a day from the timestamp.
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies a Datetime column. |
| Data column |
|---|
| 2009-07-30 00 03 00 |
| Function |
Result |
|---|---|
| millisecond_of_day(Data column) |
180000 |
Returns the millisecond of a second from the timestamp.
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies a Datetime column. |
| Data column |
|---|
| 2009-07-30 12 58 59 850 |
| Function |
Result |
|---|---|
| millisecond_of_second(Data column) |
850 |
Returns true for a leap year and false otherwise.
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column . |
| Data column |
|---|
| 2016-04-09 |
| Function |
Result |
|---|---|
| is_leap_year(Date column) |
true |
Extracts month from the date/timestamp.
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column. |
| Data column |
|---|
| Nov 04 |
| 2016 |
| Function |
Result |
|---|---|
| month(Data column) |
11 |
Returns the month's name of the date/timestamp.
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column. |
| Data column |
|---|
| 1997-02-28 |
| Function |
Result |
|---|---|
| month_name(Data column) |
February |
Returns the month name of the date/timestamp along with the year.
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column. |
| Data column |
|---|
| 1997-02-28 |
| Function |
Result |
|---|---|
| month_year(Data column) |
February, 1997 |
Returns the number of months between the two specified dates.
| Name |
Description |
|---|---|
|
date1 Datetime |
Specifies a Date column or a Datetime column from which the difference in months is to be determined. |
|
date2 Datetime |
Specifies a Date column or a Datetime column from which the difference in months is to be determined. |
| Data column 1 |
|---|
| 1997-02-28 |
| Data column 2 |
|---|
| 1997-10-28 |
| Function |
Result |
|---|---|
| months_between('Data column 1', 'Data column 2') |
8 |
Extract quarter of the year, in the range 1 to 4 from the date.
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column. |
| Data column |
|---|
| 2016-08-31 |
| Function |
Result |
|---|---|
| quarter(Data column) |
3 |
Extract quarter of the year, in the range 1 to 4, with the name of the year from date.
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column. |
| Data column |
|---|
| 2016-08-31 |
| Function |
Result |
|---|---|
| quarter_name(Data column) |
Q3 |
Extract quarter of the year, in the range 1 to 4, with the year from the date.
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column. |
| Data column |
|---|
| 2016-08-31 |
| Function |
Result |
|---|---|
| quarter_year(Data column) |
Q3, 2016 |
Returns the seconds component of the date time. The function also supports unix timestamp in milliseconds as the input.
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies a Datetime column. |
| Data column |
|---|
| 2009-07-30 12:58:59 |
| Function |
Result |
|---|---|
| second(Data column) |
59 |
Returns the second component of a day from the timestamp. The function also supports unix timestamp in milliseconds as the input.
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies a Datetime column. |
| Data column |
|---|
| 2009-07-30 00 11 00 |
| Function |
Result |
|---|---|
| second_of_day(Data column) |
660 |
Returns the week of year (in number) from the date or timestamp (a week is considered to start on a Monday and week 1 is the first week with more than 3 days).
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column. |
|
startDay Number |
[Optional] Specifies the starting day of the week. The value range from 1 to 7 in this order. [1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday]. The default value is 1. |
|
week_mode Number |
[Optional] Week mode determines how the function works. Values allowed are '1' (ISO Week Mode - Minimal 4 days), '2' (week starts from January 1st of the calendar year). The default value is 1. |
| Data column |
|---|
| 06/12/2018 |
| Function |
Result |
|---|---|
| week_of_year(Data column, 1, 1) |
49 |
Returns the week of the month of the given date.
| Name |
Description |
|---|---|
|
date Datetime |
Specifies the Date or a Datetime. |
| Data column |
|---|
| 06/13/2018 |
| Function |
Result |
|---|---|
| week_of_month(Data column) |
Week 2 |
Returns the week of the year with year from the given date
| Name |
Description |
|---|---|
|
datetime Datetime |
Parameter must be a Datetime column |
|
startDay Number |
[Optional] Specifies the starting day of the week. The value range from 1 to 7 in this order. [1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday]. The default value is 1. |
|
week_mode Number |
[Optional] Week mode determines how the function works. Values allowed are '1' (ISO Week Mode - Minimal 4 days), '2' (week starts from January 1st of the calendar year). The default value is 1. |
| Data column |
|---|
| 06 Dec 2018 |
| 17 Nov 2019 |
| Function |
Result |
|---|---|
| week_of_year_with_year(Data column, 1, 1) |
Week 49 2018, Week 46 2019 |
Returns the year from the given date
| Name |
Description |
|---|---|
|
date Datetime |
Parameter must be a Date or a Datetime column |
| Data column |
|---|
| 06 Dec 2018 |
| 17 Nov 2019 |
| Function |
Result |
|---|---|
| year(Data column) |
2018, 2019 |
Returns the century of the given Date or the Datetime input.
| Name |
Description |
|---|---|
|
date Datetime |
Parameter must be of date type or a Datetime type. |
| Data column |
|---|
| 06 Dec 2018 |
| 17 Nov 2019 |
| Function |
Result |
|---|---|
| century(Data column) |
21 |
Returns the date part from the given date column, or a Datetime column.
| Name |
Description |
|---|---|
|
datetime Datetime |
Parameter must be of Datetime type or an expression that returns a Datetime. |
| Data column |
|---|
| 2009-03-17 20 18 34 543 |
| Function |
Result |
|---|---|
| extract_date(Data column) |
2009-03-17 |
Returns the time part from the given Datetime column.
| Name |
Description |
|---|---|
|
datetime Datetime |
Parameter must be of Datetime type or an expression that returns a Datetime. |
| Data column |
|---|
| 2009-05-07 12 03 34 |
| Function |
Result |
|---|---|
| extract_time(Data column) |
12:03:34 |
Adds the specified number of years to the given Datetime value.
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies the Datetime column to increment. |
|
num_of_years Decimal |
Specifies the number of years by which to increment the timestamp value. |
| Data column |
|---|
| 03/25/2009 |
| Function |
Result |
|---|---|
| add_years(Data column, '3') |
03/25/2012 |
Subtracts the specified number of years from the given Datetime value.
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies the Datetime column or the exact date to decrement. |
|
num_of_years Decimal |
Specifies the number of years by which to decrement the timestamp value. |
| Data column |
|---|
| 06/12/2018 |
| Function |
Result |
|---|---|
| sub_years(Data column, '3') |
06/12/2015 |
Returns the number of years between startDate and endDate.
| Name |
Description |
|---|---|
|
startDate Datetime |
Specifies a Date column or a Datetime column, the exact start date or an expression that results in a date value. |
|
endDate Datetime |
Specifies a Date column or a Datetime column, the exact end date or an expression that results in a date value. |
| Data column 1 |
|---|
| 2013-08-10 |
| Data column 2 |
|---|
| 2009-03-07 |
| Function |
Result |
|---|---|
| diff_in_years(Data column 1, Data column 2) |
4 |
Adds the specified number of weeks to the given Datetime value.
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies the Datetime column to increment. |
|
num_of_weeks Decimal |
Specifies the number of weeks by which to increment the timestamp value. |
| Data column |
|---|
| 03/25/2009 |
| Function |
Result |
|---|---|
| add_weeks(Data column, '2') |
04/08/2009 |
Subtracts the specified number of weeks in the given Datetime value.
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies the Datetime column or the exact date to decrement. |
|
num_of_weeks Decimal |
Specifies the number of weeks by which to decrement the timestamp value. |
| Data column |
|---|
| 06/12/2018 |
| Function |
Result |
|---|---|
| sub_weeks(Data column, '2') |
11/22/2018 |
Returns the number of weeks between startDate and endDate.
| Name |
Description |
|---|---|
|
startDate Datetime |
Specifies a Date column or a Datetime column, the exact start date or an expression that results in a date value. |
|
endDate Datetime |
Specifies a Date column or a Datetime column, the exact end date or an expression that results in a date value. |
| Data column 1 |
|---|
| 2009-08-10 |
| Data column 2 |
|---|
| 2009-09-17 |
| Function |
Result |
|---|---|
| diff_in_weeks(Data column 1, Data column 2) |
7 |
Adds the specified number of months to the specified date value
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column |
|
num_of_months Decimal |
Specifies the number of months to add to the date value |
| Data column |
|---|
| 2009-11-25 |
| Function |
Result |
|---|---|
| add_months(Data column, `3`) |
2010-02-25 |
Subtracts the specified number of months from the given date value.
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies a Date column or a Datetime column. |
|
num_of_months Decimal |
Specifies the number of months by which to decrement the date. |
| Data column |
|---|
| 19/12/2016 |
| Function |
Result |
|---|---|
| sub_months(Data column, '3') |
19/09/2016 |
Returns the number of months between startDate and endDate.
| Name |
Description |
|---|---|
|
startDate Datetime |
Specifies a Date column or a Datetime column. |
|
endDate Datetime |
Specifies a Date column or a Datetime column. |
| Data column 1 |
|---|
| 2009-08-17 |
| Data column 2 |
|---|
| 2009-03-10 |
| Function |
Result |
|---|---|
| diff_in_months(Data column 1, Data column 2) |
5 |
Adds the specified number of days to the specified date value.
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column to increment. |
|
num_of_days Decimal |
Specifies the number of days by which to increment the dates. |
| Data column |
|---|
| 12/25/2009 |
| Function |
Result |
|---|---|
| add_days(Data column, `30`) |
01/24/2010 |
Subtracts the specified number of days from the specified date value.
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column to decrement. |
|
num_of_days Decimal |
Specifies the number of days by which to decrement the date. |
| Data column |
|---|
| 2009-12-25 |
| Function |
Result |
|---|---|
| sub_days(Data column, '5') |
2009-12-20 |
Returns the number of days between startDate and endDate.
| Name |
Description |
|---|---|
|
startDate Datetime |
Specifies a Date column or a Datetime column. |
|
endDate Datetime |
Specifies a Date column or a Datetime column. |
| Data column 1 |
|---|
| 2009-03-10 |
| Data column 2 |
|---|
| 2009-03-07 |
| Function |
Result |
|---|---|
| diff_in_days(Data column 1, Data column 2) |
3 |
Adds the specified number of hours to the given Date column or a Datetime column.
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies the Datetime column to increment. |
|
num_of_hours Decimal |
Specifies the number of hours by which the value will be incremented. |
| Data column |
|---|
| 12/08/2018 07:58:59 |
| Function |
Result |
|---|---|
| add_hours(Data column, '3') |
12/08/2018 10:58:59 |
Subtracts the specified number of hours from the given date value.
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies the Datetime column to decrement. |
|
num_of_hours Decimal |
Specifies the number of hours by which to decrement the timestamp value. |
| Data column |
|---|
| 1965-07-27 11 58 59 |
| Function |
Result |
|---|---|
| sub_hours(Data column, 3) |
1965-07-27 8:58:59 |
Returns the number of hours between startDate timestamp and endDate timestamp.
| Name |
Description |
|---|---|
|
startDateTimestamp Datetime |
Specifies a Datetime column. |
|
endDateTimestamp Datetime |
Specifies a Datetime column. |
| Data column 1 |
|---|
| 2009-03-07 23 20 40 600 |
| Data column 2 |
|---|
| 2009-03-07 20 18 34 543 |
| Function |
Result |
|---|---|
| diff_in_hours(Data column 1, Data column 2) |
3 |
Adds the specified number of minutes to the given Datetime value.
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies the Datetime column to increment. |
|
num_of_minutes Decimal |
Specifies the number of minutes by which to increment the timestamp value. |
| Data column |
|---|
| 12/08/2018 11:58:59 |
| Function |
Result |
|---|---|
| add_minutes(Data column, '2') |
12/08/2018 12:00:59 |
Subtracts the specified number of minutes from the given Datetime value.
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies the Datetime column to decrement. |
|
num_of_minutes Decimal |
Specifies the number of minutes by which to decrement the timestamp value. |
| Data column |
|---|
| 2018-07-18 00 10 00 |
| Function |
Result |
|---|---|
| sub_minutes(Data column, 2) |
2018-07-18 00:08:00 |
Returns the number of minutes between startDate timestamp and endDate timestamp.
| Name |
Description |
|---|---|
|
startDateTimestamp Datetime |
Specifies a Datetime column. |
|
endDateTimestamp Datetime |
Specifies a Datetime column. |
| Data column 1 |
|---|
| 2009-03-07 20 20 34 600 |
| Data column 2 |
|---|
| 2009-03-07 20 18 34 543 |
| Function |
Result |
|---|---|
| diff_in_minutes(Data column1, Data column 2) |
2 |
Adds the specified number of seconds to the given Datetime value.
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies the Datetime column to increment. |
|
num_of_seconds Decimal |
Specifies the number of seconds by which to increment the timestamp value. |
| Data column |
|---|
| 03/25/2009 11:58:49 |
| Function |
Result |
|---|---|
| add_seconds(Data column, '10') |
03/25/2009 11:58:59 |
Subtracts the specified number of seconds in the given Datetime value.
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies the Datetime column to decrement. |
|
num_of_seconds Decimal |
Specifies the number of seconds by which to decrement the timestamp value. |
| Data column |
|---|
| 06/12/2018 00 03 00 |
| Function |
Result |
|---|---|
| sub_seconds(Data column, '10') |
06/12/2018 00:02:50 |
Returns the number of milliseconds between startDate timestamp and endDate timestamp.
| Name |
Description |
|---|---|
|
startDateTimestamp Datetime |
Specifies a Datetime column. |
|
endDateTimestamp Datetime |
Specifies a Datetime column. |
| Data column 1 |
|---|
| 2009-03-07 20 20 36 600 |
| Data column 2 |
|---|
| 2009-03-07 20 20 34 543 |
| Function |
Result |
|---|---|
| diff_in_seconds(Data column 1, Data column 2) |
02 |
Returns the UNIX timestamp of the given date time values.
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column. |
| Data column |
|---|
| 06/12/2018 00 03 00 |
| Function |
Result |
|---|---|
| to_unix_timestamp(Data column) |
1544034780 |
Returns the date component from date column in the given format.
| Name |
Description |
|---|---|
|
datetime Datetime |
Parameter must be a Date column or a Datetime column. |
|
format Text |
Specifies the format in which the date should be extracted by the function. |
| Data column |
|---|
| 2009-05-17 |
| Function |
Result |
|---|---|
| extract_date_with_format(Data column, 'dd-MM-yyyy') |
17-05-2009 |
Returns the time component from Datetime column in the given format.
| Name |
Description |
|---|---|
|
datetime Datetime |
Parameter is mandatory and it must be a Datetime type column. |
|
format Text |
Parameter is mandatory and it must be a Text representing the time format to extract. |
| Data column |
|---|
| 2009-05-07 12 03 34 433 |
| Function |
Result |
|---|---|
| extract_time_with_format(Data column, 'hh:mm:ss:SSS') |
12:03:34:433 |
Returns true if the given date value is within the previous N days from today
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column |
|
num Number |
Specifies the number of days |
| Data column |
|---|
| 06/15/2020 |
| Function |
Result |
|---|---|
| is_last_nday(Data column, 10) |
false |
Returns true if the given date value is within the previous N months from the current month
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column |
|
num Number |
Specifies the number of months |
| Data column |
|---|
| 05/15/2020 |
| Function |
Result |
|---|---|
| is_last_nmonth(Data column, 3) |
false |
Returns true if the given date value is within the previous N quarters from the current quarter
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column |
|
num Number |
Specifies the number of quarters |
| Data column |
|---|
| 05/15/2020 |
| Function |
Result |
|---|---|
| is_last_nquarter(Data column, 3) |
false |
Returns true if the given date value is within the previous N years from the current year
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column |
|
num Number |
Specifies the number of years |
| Data column |
|---|
| 06/15/2019 |
| Function |
Result |
|---|---|
| is_last_nyear(Data column, 10) |
false |
Returns true if the given Date or the Datetime value falls under the current month.
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column. |
| Data column |
|---|
| 06/15/2020 |
| Function |
Result |
|---|---|
| is_current_month(Data column) |
false |
Returns true if the given Date or the Datetime value falls under the current quarter.
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column. |
| Data column |
|---|
| 07/15/2000 |
| Function |
Result |
|---|---|
| is_current_quarter(Data column) |
false |
Returns true if the given Date or the Datetime value falls under the current week. The starting day of the week is Sunday by default. This value can be changed using the 'startDay' parameter.
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column. |
|
startDay Number |
Specifies the starting day of the week. The value range from 0 to 6 in this order. [0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday] |
| Data column |
|---|
| 06/15/2020 |
| Function |
Result |
|---|---|
| is_current_week(Data column, 1) |
false |
Returns true if the given Date or Datetime value falls under the current year.
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column. |
| Data column |
|---|
| 07/15/2000 |
| Function |
Result |
|---|---|
| is_current_year(Data column) |
false |
Returns true if the given date value falls in the next N days from today
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column |
|
num Number |
Specifies the number of days |
| Data column |
|---|
| 06/18/2020 |
| Function |
Result |
|---|---|
| is_next_nday(Data column, 10) |
false |
Returns true if the given date value falls under the next N months from this month
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column |
|
num Number |
Specifies the number of months |
| Data column |
|---|
| 06/18/2020 |
| Function |
Result |
|---|---|
| is_next_nmonth(Data column, 3) |
false |
Returns true if the given date value falls under the next N quarters from this quarter
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column |
|
num Number |
Specifies the number of quarters |
| Data column |
|---|
| 06/18/2020 |
| Function |
Result |
|---|---|
| is_next_nquarter(Data column, 1) |
false |
Returns true if the given date value falls under the next N years from this year
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column |
|
num Number |
Specifies the number of years |
| Data column |
|---|
| 06/15/2020 |
| Function |
Result |
|---|---|
| is_next_nyear(Data column, 2) |
false |
Returns start date for the given date value using the given date units
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column. |
|
unit Text |
Specifies the unit of the date column. The date units can be 'week', 'week_sunday', 'week_monday', 'month', 'quarter' and 'year'. |
| Data column |
|---|
| 28/10/2020 |
| Function |
Result |
|---|---|
| start_date(Data column, 'month') |
01/10/2020 |
Returns the end date for the given date value using the given date units.
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column over which the function is applied |
|
units Text |
Specifies the unit of the date column. The date units can be 'week', 'week_sunday', 'week_monday', 'month', 'quarter' and 'year'. |
| Data column |
|---|
| 2018-08-27 |
| Function |
Result |
|---|---|
| end_date(Data column, 'month') |
2018-08-31 |
Returns the date after the current date
| Function |
Result |
|---|---|
| tomorrow(); |
02/06/2020 |
Returns the date of the previous day from the current date
| Function |
Result |
|---|---|
| yesterday(); |
28/05/2020 |
Returns the year and week of the given date
| Name |
Description |
|---|---|
|
date Datetime |
Parameter must be a Date column or a Datetime column |
| Data column |
|---|
| 06 Dec 2018 |
| 17 Nov 2018 |
| Function |
Result |
|---|---|
| year_week(Data column); |
201848, 201846 |
Returns the day the current week begins with
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column. |
|
startDay Number |
Specifies the starting day of the week. The value range from 0 to 6 in this order. [0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday] |
| Data column |
|---|
| 11 Oct 2013 |
| Function |
Result |
|---|---|
| first_date_current_week(`Data column`, 4) |
09 Oct 2013 |
Returns true if the given date value falls in the previous week based on the start day of the week
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column |
|
startDay Number |
Specifies the starting day of the week. The value range from 0 to 6 in this order. [0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday] |
| Data column |
|---|
| 10 Nov 2100 |
| Function |
Result |
|---|---|
| is_previous_week(`Data column`, 0) |
false |
Returns true if the given date value falls in the next week based on the start day of the week
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column |
|
startDay Number |
Specifies the starting day of the week. The value range from 0 to 6 in this order. [0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday] |
| Data column |
|---|
| 20 Nov 1900 |
| Function |
Result |
|---|---|
| is_next_week(`Data column`, 1) |
false |
Returns the current time. Function accepts no parameters.
| Function |
Result |
|---|---|
| current_time() |
19:18:55 |
Returns date in the required format if the date value to be parsed falls under any of the given input formats
| Name |
Description |
|---|---|
|
date Text |
Specifies the input column with dates in different formats. The column can be of any datatype. |
|
outputFormat Text |
Specifies the output date format that the given date should be parsed to. |
|
inputFormats Text |
Accepts multiple parameters where each specify the existing date formats present in the input column. |
| dateCol |
|---|
| Mon 23 Mar 2020 |
| 17/02/2020 |
| 03/21/2020 |
| 04/24/2020 16_25 |
| Function |
Result |
|---|---|
| parse_date(dateCol, 'EEE dd MMM,yyyy', 'dd/MM/yyyy', 'MM/dd/yyyy', 'MM/dd/yyyy', MM/dd/yyyy HH_mm) |
Mon 23 Mar,2020, Mon 17 Feb,2020, Sat 21 Mar,2020 |
Returns the date and time value in converted timezone
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies the Datetime column. |
|
current_tz Text |
The current time zone value should be in 'hh:mm' format |
|
convert_tz Text |
The difference in time between the current time zone and the alternate zone |
| Data column |
|---|
| 2004-01-01 12 00 00 |
| Function |
Result |
|---|---|
| convert_timezone(Data column,'+00:00','+10:00') |
2004-01-01 22:00:00 |
Converts the given seconds into time and returns it in the format HH:mm:ss. If given number is negative, a minus sign will be added before the result.
| Name |
Description |
|---|---|
|
seconds Number |
Specifies the date time column or the exact number of seconds to be converted. The parameter can also be unix timestamp in milliseconds. |
| Function |
Result |
|---|---|
| sec_to_time('86399') |
23:59:59 |
Returns the microsecond value from the given Datetime value
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies a Datetime column |
| Data column |
|---|
| 1990-08-07 10 35 23 3427 |
| Function |
Result |
|---|---|
| microsecond(Data column) |
342700 |
Returns the date value by adding the number of days to the given year
| Name |
Description |
|---|---|
|
year Number |
Specifies the year which must be a number, a numeric column or an expression that returns a number |
|
numOfDays Number |
Specifies the number of days which must be a number, a numeric column or an expression that returns a number |
| Function |
Result |
|---|---|
| make_date('2020', '30') |
01/30/2020 |
Adds the specified time to the given Datetime column.
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies a Datetime column. |
|
time Text |
Specifies the time to increase the Datetime. |
| Data column |
|---|
| 12/08/2018 07 58 59 |
| Function |
Result |
|---|---|
| add_time(Data column, '01:00:00') |
12/08/2018 08:58:59 |
Returns the Datetime by subtracting the specified time from the given value with time value
| Name |
Description |
|---|---|
|
datetime Datetime |
Specifies a Datetime column |
|
time Text |
Specifies the time to decrement from the Datetime value. Value can be in the format `HH:mm:ss` |
| Data column |
|---|
| 12/08/2018 07 58 59 |
| Function |
Result |
|---|---|
| sub_time(Data column, '01:00:00') |
12/08/2018 06:58:59 |
Adds the specified number of months to the first parameter and returns in the 'year-month' format
| Name |
Description |
|---|---|
|
yearMonth Number |
Parameter must be a Number column or a number, or an expression that returns a number |
|
numOfMonths Number |
Parameter must be a Number column or a number, or an expression that returns a number |
| Function |
Result |
|---|---|
| period_add(20208,6) |
20212 |
Returns the difference between two periods in months
| Name |
Description |
|---|---|
|
yearMonth1 Number |
Parameter must be a Number column or a number, or an expression that returns a number |
|
yearMonth2 Number |
Parameter must be a Number column or a number, or an expression that returns a number |
| Function |
Result |
|---|---|
| period_diff('198608','198602') |
6 |
Returns the financial year from the date specifying the fiscal start month.
| Name |
Description |
|---|---|
|
date Datetime |
Specifies a Date column or a Datetime column. |
|
month Number |
Specifies the fiscal year starting month. |
| Data column |
|---|
| 09/25/2017 |
| Function |
Result |
|---|---|
| financial_year(Data column, '7') |
2018 |
Returns the total number of minutes of the given time value.
| Name |
Description |
|---|---|
|
time Datetime |
Specifies a Time or a Datetime column. |
| Data column |
|---|
| 11:22:33 |
| Function |
Result |
|---|---|
| time_to_minute('Data column) |
682 |
Returns the time value using the given number of hours, minutes, and seconds.
| Name |
Description |
|---|---|
|
num_of_hours Number |
Specifies the number of hours in the time value. |
|
num_of_minutes Number |
Specifies the number of minutes in the time value. |
|
num_of_seconds Number |
Specifies the number of seconds in the time value. |
| Function |
Result |
|---|---|
| make_time('11','23','25') |
11:23:25 |
Returns true if the given time falls within the current hour, false otherwise.
| Name |
Description |
|---|---|
|
time Datetime |
Specifies a Time or a Datetime column. |
| Data column |
|---|
| 02:35:54 |
| Function |
Result |
|---|---|
| is_current_hour(Data column) |
false |
Returns true if the given hour falls under the last N number of hours from the current hour.
| Name |
Description |
|---|---|
|
time Datetime |
Specifies a Time or a Datetime column. |
|
num_of_hours Number |
Specifies the number of hours. |
| Data column |
|---|
| 02:35:54 |
| Function |
Result |
|---|---|
| is_last_nhour(Data column, 3) |
false |
Returns true if the given hour falls under the next N number of hours from the current hour.
| Name |
Description |
|---|---|
|
time Datetime |
Specifies a Time or a Datetime column. |
|
num_of_hours Number |
Specifies the number of hours. |
| Data column |
|---|
| 02:35:54 |
| Function |
Result |
|---|---|
| is_next_nhour(Data column, 3) |
true |
Returns the starting hour of the given time value.
| Name |
Description |
|---|---|
|
time Datetime |
Specifies a Time or a Datetime column. |
| Data column |
|---|
| 10:35:23 |
| Function |
Result |
|---|---|
| start_of_hour(Data column) |
10:00:00 |
Returns the ending hour of the given time value.
| Name |
Description |
|---|---|
|
time Datetime |
Specifies a Time or a Datetime column. |
| Data column |
|---|
| 10:35:23 |
| Function |
Result |
|---|---|
| end_of_hour(Data column) |
11:00:00 |
Returns the sum of two time values in the default duration format.
| Name |
Description |
|---|---|
|
time1 Datetime |
Specifies a Time or a Datetime column. |
|
time2 Datetime |
Specifies a Time or a Datetime column. |
| Data column 1 |
|---|
| 10:00:00 |
| Data column 2 |
|---|
| 20:00:00 |
| Function |
Result |
|---|---|
| time_sum_in_duration('Data column 1', 'Data column 2') |
1.6:0:0.0 |
Returns the difference between two time values in the default duration format.
| Name |
Description |
|---|---|
|
time1 Datetime |
Specifies a Time or a Datetime column. |
|
time2 Datetime |
Specifies a Time or a Datetime column. |
| Data column 1 |
|---|
| 20:00:00 |
| Data column 2 |
|---|
| 10:00:00 |
| Function |
Result |
|---|---|
| time_diff_in_duration('Data column 1', 'Data column 2') |
0.10:0:0.0 |
Returns the date that is num_days after start_date.
| Name |
Description |
|---|---|
|
start_date Datetime |
The starting date from which the calculation begins. |
|
num_days Number |
The number of days to add to the start_date. |
| Function |
Result |
|---|---|
| date_add('2016-07-30', 1) |
2016-07-31 |
Returns the date that is num_days before start_date.
| Name |
Description |
|---|---|
|
start_date Datetime |
The starting date from which the calculation begins. |
|
num_days Number |
The number of days to subtract from the start_date. |
| Function |
Result |
|---|---|
| date_sub('2016-07-30', 1) |
2016-07-29 |
Returns timestamp ts truncated to the unit specified by the format model fmt.
| Name |
Description |
|---|---|
|
fmt Text |
The format representing the unit to truncate to. Supported values: YEAR, YYYY, YY, QUARTER, MONTH, MM, MON, WEEK, DAY, DD, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND. |
|
ts Datetime |
A datetime value or a valid timestamp string to be truncated. |
| Function |
Result |
|---|---|
| date_trunc('YEAR', '2015-03-05T09:32:05.359') |
2015-01-01 00:00:00 |
| date_trunc('MM', '2015-03-05T09:32:05.359') |
2015-03-01 00:00:00 |
| date_trunc('DD', '2015-03-05T09:32:05.359') |
2015-03-05 00:00:00 |
| date_trunc('HOUR', '2015-03-05T09:32:05.359') |
2015-03-05 09:00:00 |
| date_trunc('MILLISECOND', '2015-03-05T09:32:05.123456') |
2015-03-05 09:32:05.123 |
Returns the number of days from startDate to endDate.
| Name |
Description |
|---|---|
|
endDate Datetime |
The end date expression or valid date string. |
|
startDate Datetime |
The start date expression or valid date string. |
| Function |
Result |
|---|---|
| datediff('2009-07-31', '2009-07-30') |
1 |
| datediff('2009-07-30', '2009-07-31') |
-1 |
Converts a UNIX timestamp to a string representation using the specified format.
| Name |
Description |
|---|---|
|
unix_time Decimal |
UNIX timestamp to be converted to the provided format. |
|
fmt Text |
[Optional] Date/time format pattern to follow. Defaults to 'yyyy-MM-dd HH:mm:ss' if omitted. |
| Function |
Result |
|---|---|
| from_unixtime(0, 'yyyy-MM-dd HH:mm:ss') |
1969-12-31 16:00:00 |
| from_unixtime(0) |
1969-12-31 16:00:00 |
Returns the greatest (latest) date or timestamp value among the given expressions, skipping null values.
| Name |
Description |
|---|---|
|
Datetime Datetime |
List of date or timestamp expressions |
|
Datetime Datetime |
List of date or timestamp expressions |
| Function |
Result |
|---|---|
| greatest_date(2022-06-15, NULL, 2022-07-01); |
2022-07-01 |
Returns the least (earliest) date or timestamp value among the given expressions, skipping null values.
| Name |
Description |
|---|---|
|
Datetime Datetime |
List of date or timestamp expressions |
|
Datetime Datetime |
List of date or timestamp expressions |
| Function |
Result |
|---|---|
| least_date(2022-06-15, NULL, 2022-05-01); |
2022-05-01 |
Returns the number of years from the given duration value.
| Name |
Description |
|---|---|
|
duration Datetime |
Specifies the Duration column. |
| Data column |
|---|
| 400.10:35:23 |
| Function |
Result |
|---|---|
| duration_to_years(Data column) |
1 |
Returns the number of months from the given duration value.
| Name |
Description |
|---|---|
|
duration Datetime |
Specifies the Duration column. |
| Data column |
|---|
| 800.10:35:23 |
| Function |
Result |
|---|---|
| duration_to_months(Data column) |
26 |
Returns the number of weeks from the given duration value.
| Name |
Description |
|---|---|
|
duration Datetime |
Specifies the Duration column. |
| Data column |
|---|
| 800.10:35:23 |
| Function |
Result |
|---|---|
| duration_to_weeks(Data column) |
114 |
Returns the number of days from the given duration value.
| Name |
Description |
|---|---|
|
duration Datetime |
Specifies the Duration column. |
| Data column |
|---|
| 400.10:35:23 |
| Function |
Result |
|---|---|
| duration_to_days(Data column) |
400 |
Returns the number of hours from the given duration value.
| Name |
Description |
|---|---|
|
duration Datetime |
Specifies the Duration column. |
| Data column |
|---|
| 800.10:35:23 |
| Function |
Result |
|---|---|
| duration_to_hours(Data column) |
19120 |
Returns the number of minutes from the given duration value.
| Name |
Description |
|---|---|
|
duration Datetime |
Specifies the Duration column. |
| Data column |
|---|
| 400.10:35:23 |
| Function |
Result |
|---|---|
| duration_to_minutes(Data column) |
576635 |
Returns the number of seconds from the given duration value.
| Name |
Description |
|---|---|
|
duration Datetime |
Specifies the Duration column. |
| Data column |
|---|
| 400.10:35:23 |
| Function |
Result |
|---|---|
| duration_to_seconds(Data column) |
34598123 |
Returns the duration value by adding the given number of years.
| Name |
Description |
|---|---|
|
duration Datetime |
Specifies the Duration column. |
|
num_of_years Number |
The number of years to add to the duration value. |
| Data column |
|---|
| 1000 days 11:22:33 |
| Function |
Result |
|---|---|
| add_years_to_duration(Data column, 5) |
2825 days 11:22:33 |
Returns the duration value by adding the given number of months.
| Name |
Description |
|---|---|
|
duration Datetime |
Specifies the Duration column. |
|
num_of_months Number |
The number of months to add to the duration value. |
| Data column |
|---|
| 1000 days 11:22:33 |
| Function |
Result |
|---|---|
| add_months_to_duration(Data column, 7) |
1210 days 11:22:33 |
Returns the duration value by adding the given number of weeks.
| Name |
Description |
|---|---|
|
duration Datetime |
Specifies the Duration column. |
|
num_of_weeks Number |
The number of weeks to add to the duration value. |
| Data column |
|---|
| 1000 days 11:22:33 |
| Function |
Result |
|---|---|
| add_weeks_to_duration(Data column, 8) |
1056 days 11:22:33 |
Returns the duration value by adding the given number of days.
| Name |
Description |
|---|---|
|
duration Datetime |
Specifies the Duration column. |
|
num_of_days Number |
The number of days to add to the duration value. |
| Data column |
|---|
| 1000 days 11:22:33 |
| Function |
Result |
|---|---|
| add_days_to_duration(Data column, 8) |
1008 days 11:22:33 |
Returns the duration value by adding the given number of hours.
| Name |
Description |
|---|---|
|
duration Datetime |
Specifies the Duration column. |
|
num_of_hours Number |
The number of hours to add to the duration value. |
| Data column |
|---|
| 1000 days 11:22:33 |
| Function |
Result |
|---|---|
| add_hours_to_duration(Data column, 5) |
1000 days 16:22:33 |
Returns the duration value by adding the given number of minutes.
| Name |
Description |
|---|---|
|
duration Datetime |
Specifies the Duration column. |
|
num_of_minutes Number |
The number of minutes to add to the duration value. |
| Data column |
|---|
| 1000 days 11:22:33 |
| Function |
Result |
|---|---|
| add_minutes_to_duration(Data column, 5) |
1000 days 11:27:33 |
Returns the duration value by adding the given number of seconds.
| Name |
Description |
|---|---|
|
duration Datetime |
Specifies the Duration column. |
|
num_of_seconds Number |
The number of seconds to add to the duration value. |
| Data column |
|---|
| 1000 days 11:22:33 |
| Function |
Result |
|---|---|
| add_seconds_to_duration(Data column, 5) |
1000 days 11:22:38 |
Returns the duration value by subtracting the given number of years.
| Name |
Description |
|---|---|
|
duration Datetime |
Specifies the Duration column. |
|
num_of_years Number |
The number of years to subtract from the duration value. |
| Data column |
|---|
| 1000 days 11:22:33 |
| Function |
Result |
|---|---|
| sub_years_from_duration(Data column, 1) |
635 days 11:22:33 |
Returns the duration value by subtracting the given number of months.
| Name |
Description |
|---|---|
|
duration Datetime |
Specifies the Duration column. |
|
num_of_months Number |
The number of months to subtract from the duration value. |
| Data column |
|---|
| 1000 days 11:22:33 |
| Function |
Result |
|---|---|
| sub_months_to_duration(Data column, 3) |
910 days 11:22:33 |
Returns the duration value by subtracting the given number of weeks.
| Name |
Description |
|---|---|
|
duration Datetime |
Specifies the Duration column. |
|
num_of_weeks Number |
The number of weeks to subtract from the duration value. |
| Data column |
|---|
| 1000 days 11:22:33 |
| Function |
Result |
|---|---|
| sub_weeks_from_duration(Data column, 10) |
930 days 11:22:33 |
Returns the duration value by subtracting the given number of days.
| Name |
Description |
|---|---|
|
duration Datetime |
Specifies the Duration column. |
|
num_of_days Number |
The number of days to subtract from the duration value. |
| Data column |
|---|
| 1000 days 11:22:33 |
| Function |
Result |
|---|---|
| sub_days_from_duration(Data column, 30) |
970 days 11:22:33 |
Returns the duration value by subtracting the given number of hours.
| Name |
Description |
|---|---|
|
duration Datetime |
Specifies the Duration column. |
|
num_of_hours Number |
The number of hours to subtract from the duration value. |
| Data column |
|---|
| 1000 days 11:22:33 |
| Function |
Result |
|---|---|
| sub_hours_from_duration(Data column, 3) |
1000 days 9:22:33 |
Returns the duration value by subtracting the given number of minutes.
| Name |
Description |
|---|---|
|
duration Datetime |
Specifies the Duration column. |
|
num_of_minutes Number |
The number of minutes to subtract from the duration value. |
| Data column |
|---|
| 1000 days 11:22:33 |
| Function |
Result |
|---|---|
| sub_minutes_from_duration(Data column, 10) |
1000 days 11:12:33 |
Returns the duration value by subtracting the given number of seconds.
| Name |
Description |
|---|---|
|
duration Datetime |
Specifies the Duration column. |
|
num_of_seconds Number |
The number of seconds to subtract from the duration value. |
| Data column |
|---|
| 1000 days 11:22:33 |
| Function |
Result |
|---|---|
| sub_seconds_from_duration(Data column, 10) |
1000 days 11:22:23 |
Returns the difference of two timestamps value in duration.
| Name |
Description |
|---|---|
|
date1 Datetime |
Specifies the Date, Time, or a Datetime column. |
|
date2 Datetime |
Specifies the Date, Time, or a Datetime column. |
| Data column 1 |
|---|
| 17-08-1991 16:15:14 |
| Data column 2 |
|---|
| 17-08-1990 14:15:16 |
| Function |
Result |
|---|---|
| timestamp_diff_in_duration('Data column 1', 'Data column 2') |
365.1:59:58.0 |
Returns the duration value using the given number of years, months, weeks, days, hours, minutes, and seconds. Enter 0 if no column or value is specified for any of the parameters.
| Name |
Description |
|---|---|
|
num_of_years Number |
Specifies the number of years in the duration value. |
|
num_of_months Number |
Specifies the number of months in the duration value. |
|
num_of_weeks Number |
Specifies the number of weeks in the duration value. |
|
num_of_days Number |
Specifies the number of days in the duration value. |
|
num_of_hours Number |
Specifies the number of hours in the duration value. |
|
num_of_minutes Number |
Specifies the number of minutes in the duration value. |
|
num_of_seconds Number |
Specifies the number of seconds in the duration value. |
| Function |
Result |
|---|---|
| make_duration('12','11','23','11','9','10','5') |
4882.9:10:5.0 |
If the condition is true, then returns param1; otherwise returns param2.
| Name |
Description |
|---|---|
|
condition Boolean |
An expression that returns a Boolean. |
|
param1 Text |
Parameter can be any value or a column of any type. |
|
param2 Text |
Parameter can be any value or a column of any type. |
| Function |
Result |
|---|---|
| if(1 < 2, 'a', 'b') |
a |
Returns param2 if param1 is null, returns param1 otherwise.
| Name |
Description |
|---|---|
|
param1 Text |
Parameter can be any value or a column of any type. |
|
param2 Text |
Parameter can be any value or a column of any type. |
| Function |
Result |
|---|---|
| if_null(null, 'b') |
b |
Returns true if the expression or column value is null or empty
| Name |
Description |
|---|---|
|
param Text |
Parameter can be a column, or an expression |
| Function |
Result |
|---|---|
| is_null(null) |
true |
| is_null('A') |
false |
Returns true if the column value or expression is not null or empty
| Name |
Description |
|---|---|
|
param Text |
Parameter can be a column, or an expression |
| Function |
Result |
|---|---|
| is_not_null(null) |
false |
| is_not_null('A') |
true |
Applies the NOT logical operation on the column. Returns the opposite value of the Boolean value specified.
| Name |
Description |
|---|---|
|
param Boolean |
Specifies a Boolean value on which the not logical operation is to be applied on. |
| Function |
Result |
|---|---|
| not(5 > 3) |
false |
Returns true for missing values, false otherwise.
| Name |
Description |
|---|---|
|
param Text |
Parameter can be a column of any datatype. |
| Sales |
|---|
| 11 |
| 12 |
| ' ' |
| 67 |
| Function |
Result |
|---|---|
| is_missing(Sales) |
[false, false, true, false] |
Returns true for valid values; false otherwise.
| Name |
Description |
|---|---|
|
param1 Text |
Parameter must be a column conforming to any one data type. |
|
param2 Text |
Data type to verify if param1 is valid. It can be one of these values: Text, Date, Number, Positive number, Percentage, Datetime, Time, Timestamp, Duration, Decimal, Currency, Length, Temperature, Boolean, Email, URL, List, Map, and Custom Data Types |
| Column1 |
|---|
| "ABC" |
| "DEF" |
| "GHI" |
| Function |
Result |
|---|---|
| is_valid(`Column1`,'text') |
TRUE |
Returns true for invalid values; false otherwise.
| Name |
Description |
|---|---|
|
param1 Text |
Parameter must be a column conforming to any one data type. |
|
param2 Text |
Data type to verify if param1 is invalid. It can be one of these values: Text, Date, Number, Positive number, Percentage, Datetime, Time, Timestamp, Duration, Decimal, Currency, Length, Temperature, Boolean, Email, URL, List, Map, and Custom Data Types |
| Column1 |
|---|
| "ABC" |
| "DEF" |
| "GHI" |
| Function |
Result |
|---|---|
| is_invalid(`Column1`, 'Number') |
TRUE |
Returns the first non-null argument from the given list of expressions. If all arguments are null, returns null.
| Name |
Description |
|---|---|
|
expr Text |
List of expressions. Returns the first non-null expression. |
|
expr Text |
List of expressions. Returns the first non-null expression. |
| Function |
Result |
|---|---|
| coalesce(NULL, 1, NULL) |
1 |
| coalesce(NULL, NULL, 'Spark') |
Spark |
Performs a logical AND operation between two boolean expressions. Returns true if both expressions are true. If one expression is false, the result is false regardless of the other.
| Name |
Description |
|---|---|
|
param1 Boolean |
The first boolean expression or condition |
|
param2 Boolean |
The second boolean expression or condition |
| Function |
Result |
|---|---|
| and(true,true) |
true |
| and(true,false) |
false |
| and(true,NULL) |
NULL |
| and(false,NULL) |
false |
Returns the character length of the text parameter.
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a Text, a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| length("NOWHERE") |
7 |
Returns a sha1 hash value as a hex text of the param.
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| sha('Zoho One') |
a74457c58f9d3e564bf534d96e1d85d6e513dac4 |
Converts a numeric column to the currency format and datatype (value conversion between currencies is not supported yet)
| Name |
Description |
|---|---|
|
col Decimal |
Parameter must be a numeric column or an expression that returns a number |
|
currency Text |
Parameter must be a Text value denoting the currency format |
| sales |
|---|
| 34.30 |
| 343.50 |
| 56.30 |
| Function |
Result |
|---|---|
| to_currency(sales, '$') |
$34.30, $343.50, $56.30 |
Converts a numeric column to the length datatype
| Name |
Description |
|---|---|
|
Column Decimal |
Parameter must be a numeric value from a column or an expression that returns a number |
|
lengthUnit Text |
Parameter must be a Text value denoting the length unit that the given value needs to be converted into |
| Column |
|---|
| 50 |
| 20 |
| 30 |
| Function |
Result |
|---|---|
| to_length(Column, 'km') |
50km, 20km, 30km |
Converts a numeric column to the temperature datatype
| Name |
Description |
|---|---|
|
col Decimal |
Parameter must be a numeric value from a column or an expression that returns a number |
|
tempUnit Text |
Parameter must be a Text value denoting the temperature unit the given value is to be converted into |
| col |
|---|
| 234.3 |
| 343.5 |
| 56.3 |
| Function |
Result |
|---|---|
| to_temperature(col, 'K') |
234.3K, 343.5K, 56.3K |
Returns a SHA-1 hash value as a hex string of the expr.
| Name |
Description |
|---|---|
|
expr Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
| Function |
Result |
|---|---|
| sha1('Spark') |
85f5955f4b27a9a4c2aab6ffe5d7189fc298b92c |
Returns a checksum of SHA-2 family as a hex text of the param.
| Name |
Description |
|---|---|
|
param Text |
Parameter must be a Text or a Text column, or an expression that returns a Text. |
|
bitLength Number |
Specifies the bit length of the output text. |
| Function |
Result |
|---|---|
| sha2('Zoho One', 256) |
d9884b4a991b495936d25876c5c371725d4a2a1a57e20bd99a95f5a88081ed36 |
Returns the size of a list. Returns -1 if null.
| Name |
Description |
|---|---|
|
param List |
Parameter must be a column of type List, or a group of values as a list. |
| Function |
Result |
|---|---|
| size(['b', 'd', 'c', 'a']) |
4 |
Returns a list that contains all the elements in the first list followed by all the elements in the second. The two input lists must contain items in the same data structure format.
| Name |
Description |
|---|---|
|
param1 List |
Specifies the first of the two lists to concatenate. |
|
param2 List |
Specifies the second of the two lists to concatenate. |
| column1 |
|---|
| [1,2,3,4,5] |
| column2 |
|---|
| ["A","B,"C","D","E" ] |
| Function |
Result |
|---|---|
| list_concat(column1, column2) |
["1","2","3","4","5","A","B","C","D","E"] |
Filters the list by removing duplicate entries from it. Returns the filtered list.
| Name |
Description |
|---|---|
|
param List |
Parameter must be of type list or a List column. |
| list |
|---|
| [30, 40, 50, 30, 70, 30, 90] |
| Function |
Result |
|---|---|
| list_unique(list) |
["40", "50", "70", "90"] |
This function compares the values of two lists, and returns only the matching elements. The two lists must contain items in the same data structure format.
| Name |
Description |
|---|---|
|
param1 List |
The list to compare from. |
|
param2 List |
A list to compare against. |
| list1 |
|---|
| [1,2,3,4,5] |
| list2 |
|---|
| ["A", 1, "B" ,2, "C", 3, "D", 4, "E"] |
| Function |
Result |
|---|---|
| list_intersect(list1, list2) |
["1", "2", "3", "4"] |
Converts two unidimensional list columns into a multi dimensional list.
| Name |
Description |
|---|---|
|
param1 List |
Parameter must be of type list or a List column. |
|
param2 List |
Parameter must be of type list or a List column. |
| list1 |
|---|
| ["A","B","C"] |
| list2 |
|---|
| ["D","E","F"] |
| Function |
Result |
|---|---|
| list_zip(list1 , list2) |
[["A","D"],["B","E"],["C","F"]] |
Computes an array of integers beginning from 'startNum' to 'stopNum' having an interval of 'stepNum' between consecutive integers in the range. If the function generates more than 1000 values for a cell, the output is a null value and the 'stopNum' is excluded from the output. 'StepNum' must always be a positive integer. If 'startNum' is greater than 'stopNum', steps are automatically converted to negative values and the output will be a decrement range.
| Name |
Description |
|---|---|
|
startNum Number |
Parameter must be a Number column or a number. Decimal values are not supported. |
|
stopNum Number |
Parameter must be a Number column or a number. Decimal values are not supported. |
|
stepNum Number |
Specifies the step value for the range. Decimal values are not supported. |
| Function |
Result |
|---|---|
| range('2','14','2') |
[2, 4, 6, 8, 10, 12] |
| range('14','2','2') |
[14, 12, 10, 8, 6, 4] |
Converts the given parameters to a list
| Name |
Description |
|---|---|
|
params Text |
All parameters must be a Text, or a Text column, or an expression that returns a Text |
|
params Text |
All parameters must be a Text, or a Text column, or an expression that returns a Text |
| Function |
Result |
|---|---|
| nest_as_list('Arjun', 'Zip code: 308-242-0670', 'Status: New resident') |
["Arjun", "Zip code: 99501", "Status: New resident"] |
Returns a Map by combining two Lists.
| Name |
Description |
|---|---|
|
param1 List |
Parameter must be of type List or a List column. |
|
param2 List |
Parameter must be of type List or a List column. |
| list1 |
|---|
| [20, 30] |
| list2 |
|---|
| [A, B] |
| Function |
Result |
|---|---|
| lists_to_map(list1, list2) |
[20 : A , 30 : B] |
Returns the size of an array or a map.
| Name |
Description |
|---|---|
|
param List |
Expression of type array or map whose size is to be returned. |
| Function |
Result |
|---|---|
| cardinality(array('b', 'd', 'c', 'a')) |
4 |
| cardinality(map('a', 1, 'b', 2)) |
2 |
Returns an array containing the given input expressions or values.
| Name |
Description |
|---|---|
|
expr Text |
Input expressions or values to be combined into an array |
|
expr Text |
Input expressions or values to be combined into an array |
| Function |
Result |
|---|---|
| array(101, 102, 103) |
[101, 102, 103] |
Checks if one or more specified values exist in the given list. If a single value is provided, returns true if it's found. If multiple values are provided, returns true only if atleast one value is found in the array
| Name |
Description |
|---|---|
|
array List |
Parameter must be of type list or a List column. |
|
values Text |
One or more values to check for in the list. |
| Role |
|---|
| ["Admin", "Editor", "Viewer"] |
| Permissions |
|---|
| ["read", "write", "delete"] |
| Function |
Result |
|---|---|
| array_contains(Role, "Editor") |
true |
| array_contains(Role, "Guest") |
false |
| array_contains(Permissions, "read", "write") |
true |
| array_contains(Permissions, "read", "share") |
false |
Returns an array containing elements from array1 that are not present in array2, without duplicates.
| Name |
Description |
|---|---|
|
array1 List |
The input array from which elements will be retained after excluding elements in array2 |
|
array2 List |
The input array whose elements will be removed from array1 |
| array1 |
|---|
| [1, 2, 3], ["a", "b", "c"] |
| array2 |
|---|
| [1, 3, 5], ["b"] |
| Function |
Result |
|---|---|
| array_except([1, 2, 3], [1, 3, 5]); |
[2] |
| array_except(["a", "b", "c"], ["b"]); |
["a", "c"] |
Returns an array containing elements present in both array1 and array2, without duplicates.
| Name |
Description |
|---|---|
|
array1 List |
The first input array for intersection |
|
array2 List |
The second input array for intersection |
| Function |
Result |
|---|---|
| array_intersect([1, 2, 3], [1, 3, 5]); |
[1, 3] |
| array_intersect(["a", "b", "c"], ["b", "c", "d"]); |
["b", "c"] |
Converts the given parameters to a map. The first value will be the key, and the second will be the value in making each key-value pair. The arguments should be in the multiple of 2
| Name |
Description |
|---|---|
|
params Text |
Accepts multiple parameters. All parameters must be a column. |
|
params Text |
Accepts multiple parameters. All parameters must be a column. |
| Function |
Result |
|---|---|
| nest_as_map(['Name', 'Arjun', 'Zip code', '308-242-0670']) |
{'Name' : 'Arjun', 'Zip code' : '308-242-0670'} |
| nest_as_map(['Name', 'Arjun', 'Zip code', '308-242-0670', 'City']) |
(returns an error) |
Returns the keys from the map passed as the parameter to the function.
| Name |
Description |
|---|---|
|
param Object |
Parameter must be a column containing maps. |
| Map Column |
|---|
| key1; value1 |
| key2; value2 |
| key3; value3 |
| Function |
Result |
|---|---|
| keys_from_map(mapColumn) |
key1, key2, key3 |
Checks if the specified keys exist in the given object. Returns true only if all the keys are present.
| Name |
Description |
|---|---|
|
param Object |
Parameter must be of type map or a map column. |
|
keys Text |
One or more keys to check for in the object. |
| user_profile |
|---|
| { "name": "Alice" |
| "role": "admin" |
| "active": true } |
| Function |
Result |
|---|---|
| object_contains(user_profile, "name") |
true |
| object_contains(user_profile, "name", "role") |
true |
| object_contains(user_profile, "email") |
false |
| object_contains(user_profile, "name", "email") |
false |
You can use arithmetic and logical operators to combine two or more functions in a formula. You can also use these operators to customize the functions to create your formula column. The supported operators are given in the table below:
| Operator |
Description |
|---|---|
| ! |
Logical not |
| != |
Not equal to |
| % |
Modulo |
| & |
Bitwise AND |
| * |
Multiply |
| + |
Add |
| - |
Subtract |
| / |
Divide |
| < |
Less than |
| <= |
Less than or equal to |
| <=> |
Equal to |
| <> |
Not equal to |
| = |
Equal to |
| == |
Equal to |
| > |
Greater than |
| >= |
Greater than or equal to |
| ^ |
Bitwise exclusive OR |
| AND |
Logical AND |
| OR |
Logical OR |
In the numeric functions, a maximum of 20 whole numbers and 15 decimal places are supported. When your function input exceeds either of these limits, an empty value is returned.