Functions available to add formula columns
Abs
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 |
Div
Performs floating-point division. Returns a decimal value.
| Name | Description |
|---|---|
numerator Decimal | Parameter must be a number, a numeric column or an expression that returns a number. |
denominator Decimal | Parameter must be a number, a numeric column or an expression that returns a number. |
| Function | Result |
|---|---|
| div(3,2) | 1.5 |
Add
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 |
Sub
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 |
Mul
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 |
Avg
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 |
Min
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 |
Max
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 |
Max Date
Returns the maximum date value from a date or a date-time column.
| Name | Description |
|---|---|
date Datetime | Parameter must be a date column or a date-time column. |
| Data column |
|---|
| 06 Dec 2018 |
| 17 Nov 2019 |
| 20 Oct 2020 |
| Function | Result |
|---|---|
| max_date(Data column) | 20 Oct 2020 |
Min Date
Returns the minimum date value from a date or a date-time column.
| Name | Description |
|---|---|
date Datetime | Parameter must be a date column or a date-time column. |
| Data column |
|---|
| 06 Dec 2018 |
| 17 Nov 2019 |
| 20 Oct 2020 |
| Function | Result |
|---|---|
| min_date(Data column) | 06 Dec 2018 |
Sum
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 |
Mode
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 |
Count
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 |
Count with Blanks
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 |
Variance
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 |
Standard Deviation
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 |
Binary
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 |
Cube Root
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 |
Ceiling
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 |
Character
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 Multiple | Parameter must be a number, a numeric column, or an expression that returns a number. |
| Function | Result |
|---|---|
| char('65') | A |
Concat
Returns the concatenated text of the given parameters. Returns null, if any one of the parameters is null.
| Name | Description |
|---|---|
params Text | Accepts any number of parameters. Parameters must be a Text or a Text column, or an expression that returns a Text. |
| Function | Result |
|---|---|
| concat("NOW", "HERE") | NOWHERE |
Concat with Separator
Returns the concatenated text of the given parameters along with the separator. Returns null, if any one of the parameter is null.
| Name | Description |
|---|---|
params Text | The first of the params is considered as the separator, using which the rest of the parameters will be concatenated. All parameters must be Text, Text columns, or expressions that returns a Text. |
| Function | Result |
|---|---|
| concat_ws('_', 'NOW', 'HERE') | NOW_HERE |
Convert
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 |
Current Date
Returns the current date as a date value. Function accepts no parameters.
| Function | Result |
|---|---|
| current_date() | 09/21/2019 |
Current Timestamp
Returns the current timestamp as a date-time value. Function accepts no parameters.
| Function | Result |
|---|---|
| current_timestamp() | 09/19/2019 19:18:55 |
Date Format
Converts the date-timestamp to the format specified in the parameter.
| Name | Description |
|---|---|
date Datetime | Specifies a date column or a date-time 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 |
Day of Month
Returns the day of month of the date.
| Name | Description |
|---|---|
date Datetime | Specifies a date column or a date-time 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 date-time column. |
| Data column |
|---|
| 06-12-2018 |
| Function | Result |
|---|---|
| day_of_week(Data column) | 5 |
Week Day
Returns the day of the week from the given date
| Name | Description |
|---|---|
date Datetime | Parameter must be a date column or a date-time column |
| Data column |
|---|
| 06 Dec 2018 |
| Function | Result |
|---|---|
| weekday(Data column); | 3 |
Day of the Week in name
Returns the day of the week (as name) for the given date.
| Name | Description |
|---|---|
date Datetime | Specifies a date column or a date-time column. |
| Data column |
|---|
| 06 Dec 2018 |
| Function | Result |
|---|---|
| day_of_week_name(Data column) | Thursday |
Day of the Year
Returns the day of year (in number) of the date/timestamp.
| Name | Description |
|---|---|
date Datetime | Specifies a date column or a date-time column. |
| Data column |
|---|
| 25-03-2009 |
| Function | Result |
|---|---|
| day_of_year(Data column) | 84 |
Decode
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 |
Degrees
Converts radians to degrees.
| Name | Description |
|---|---|
param Decimal | Specifies the angle in radians. |
| Function | Result |
|---|---|
| degrees(3.141592653589793) | 180.0 |
Encode
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 |
Exponential
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 |
Floor
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 |
Greatest
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. |
| Function | Result |
|---|---|
| greatest(10, 9, 2, 4, 3) | 10 |
Hexadecimal
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 type. |
| Function | Result |
|---|---|
| hex(17) | 11 |
Hour
Returns the hour component of the timestamp.
| Name | Description |
|---|---|
datetime Datetime | Specifies a Time or a Datetime column. |
| Data column |
|---|
| 2009-03-07 20:18:34:543 |
| Function | Result |
|---|---|
| hour(Data column) | 20 |
If
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 |
Nested If
| Function | Result |
|---|---|
| if(1<3, if(1<2, 'a', 'b'), 'c') | a |
Else if
| Function | Result |
|---|---|
| if(3<2, 'a', if(3<4, 'b', 'c')) | b |
If Null
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 |
Initcap
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 |
Is Null
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 |
Is not Null
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 |
Not
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 |
Last Day
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 |
Least
Returns the least value of all, skipping the null values.
| Name | Description |
|---|---|
params Decimal | The parameters must 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 |
Left
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 |
Length
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 |
In
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 |
Log
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 |
Log10
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 |
Log2
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 |
Lower
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! |
Itrim
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 |
Minute
Returns the minute component of the timestamp.
| Name | Description |
|---|---|
datetime Datetime | Specifies a Time or a Datetime column. |
| Data column |
|---|
| 2009-07-30 12:58:59 |
| Function | Result |
|---|---|
| minute(Data column) | 58 |
Minute of the day
Returns the minute component of the day from the timestamp.
| Name | Description |
|---|---|
datetime Datetime | Specifies a date-time column. |
| Data column |
|---|
| 2009-07-30 12 58 59 |
| Function | Result |
|---|---|
| minute_of_day(Data column) | 778 |
Minute of the Hour
Returns the minute component of an hour from the timestamp.
| Name | Description |
|---|---|
datetime Datetime | Specifies a date-time column. |
| Data column |
|---|
| 2009-07-30 12 58 59 |
| Function | Result |
|---|---|
| minute_of_hour(Data column) | 58 |
Millisecond of the Day
Returns the millisecond of a day from the timestamp.
| Name | Description |
|---|---|
datetime Datetime | Specifies a date-time column. |
| Data column |
|---|
| 2009-07-30 00 03 00 |
| Function | Result |
|---|---|
| millisecond_of_day(Data column) | 180000 |
Millisecond of Second
Returns the millisecond of a second from the timestamp.
| Name | Description |
|---|---|
datetime Datetime | Specifies a date-time column. |
| Data column |
|---|
| 2009-07-30 12 58 59 850 |
| Function | Result |
|---|---|
| millisecond_of_second(Data column) | 850 |
Is Leap Year
Returns true for a leap year and false otherwise.
| Name | Description |
|---|---|
date Datetime | Specifies a date column or a date-time column . |
| Data column |
|---|
| 2016-04-09 |
| Function | Result |
|---|---|
| is_leap_year(Date column) | true |
Mod
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 |
Month
Extracts month from the date/timestamp.
| Name | Description |
|---|---|
date Datetime | Specifies a date column or a date-time column. |
| Data column |
|---|
| Nov 04 |
| 2016 |
| Function | Result |
|---|---|
| month(Data column) | 11 |
Month as Name
Returns the month's name of the date/timestamp.
| Name | Description |
|---|---|
date Datetime | Specifies a date column or a date-time column. |
| Data column |
|---|
| 1997-02-28 |
| Function | Result |
|---|---|
| month_name(Data column) | February |
Month and Year
Returns the month name of the date/timestamp along with the year.
| Name | Description |
|---|---|
date Datetime | Specifies a date column or a date-time column. |
| Data column |
|---|
| 1997-02-28 |
| Function | Result |
|---|---|
| month_year(Data column) | February, 1997 |
Months in-between
Returns the number of months between the two specified dates.
| Name | Description |
|---|---|
date1 Datetime | Specifies a date column or a date-time column from which the difference in months is to be determined. |
date2 Datetime | Specifies a date column or a date-time 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 |
Negative
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 |
Pi
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 |
Power
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 |
Quarter
Extract quarter of the year, in the range 1 to 4 from the date.
| Name | Description |
|---|---|
date Datetime | Specifies a date column or a date-time column. |
| Data column |
|---|
| 2016-08-31 |
| Function | Result |
|---|---|
| quarter(Data column) | 3 |
Quarter Name
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 date-time column. |
| Data column |
|---|
| 2016-08-31 |
| Function | Result |
|---|---|
| quarter_name(Data column) | Q3 |
Quarter Year
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 date-time column. |
| Data column |
|---|
| 2016-08-31 |
| Function | Result |
|---|---|
| quarter_year(Data column) | Q3, 2016 |
Radians
Converts angle in degrees to radian on the column.
| Name | Description |
|---|---|
param Decimal | angle in degrees |
| Function | Result |
|---|---|
| radians(180) | 3.1415926536 |
Random
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 |
Regex Extract
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 |
Regex Replace
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 |
Replace
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 |
Reverse
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 |
Right
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 |
Round Number
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 |
rtrim
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 |
Second
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 Time or a Datetime column. |
| Data column |
|---|
| 2009-07-30 12:58:59 |
| Function | Result |
|---|---|
| second(Data column) | 59 |
Second of the Day
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 date-time column. |
| Data column |
|---|
| 2009-07-30 00 11 00 |
| Function | Result |
|---|---|
| second_of_day(Data column) | 660 |
SHA1
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 |
Soundex
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 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 |
Substring
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. |
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 |
Trim
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 |
Upper
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 |
Week of the Year
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 date-time 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 |
Week of the Month
Returns the week of the month of the given date.
| Name | Description |
|---|---|
date Datetime | Specifies the date or a date-time. |
| Data column |
|---|
| 06/13/2018 |
| Function | Result |
|---|---|
| week_of_month(Data column) | Week 2 |
Week of the Year with Year
Returns the week of the year with year from the given date
| Name | Description |
|---|---|
datetime Datetime | Parameter must be a datetime type 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 |
Year
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 |
Century
Returns the century of the given date or the date-time input.
| Name | Description |
|---|---|
date Datetime | Parameter must be of date type or a date-time type. |
| Data column |
|---|
| 06 Dec 2018 |
| 17 Nov 2019 |
| Function | Result |
|---|---|
| century(Data column) | 21 |
Extract Date
Returns the date part from the given date column, or a date-time 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 |
Extract Time
Returns the time part from the given date-time 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 |
Add Years
Adds the specified number of years to the given date-time value.
| Name | Description |
|---|---|
datetime Datetime | Specifies the date-time 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 |
Sub years
Subtracts the specified number of years from the given date-time value.
| Name | Description |
|---|---|
datetime Datetime | Specifies the date-time 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 |
Difference in Years
Returns the number of years between startDate and endDate.
| Name | Description |
|---|---|
startDate Datetime | Specifies a date column or a date-time column, the exact start date or an expression that results in a date value. |
endDate Datetime | Specifies a date column or a date-time 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 |
Add Weeks
Adds the specified number of weeks to the given date-time value.
| Name | Description |
|---|---|
datetime Datetime | Specifies the date-time 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 |
Sub Weeks
Subtracts the specified number of weeks in the given date-time value.
| Name | Description |
|---|---|
datetime Datetime | Specifies the date-time 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 |
Difference in Weeks
Returns the number of weeks between startDate and endDate.
| Name | Description |
|---|---|
startDate Datetime | Specifies a date column or a date-time column, the exact start date or an expression that results in a date value. |
endDate Datetime | Specifies a date column or a date-time 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 |
Add Months
Adds the specified number of months to the specified date value
| Name | Description |
|---|---|
date Datetime | Specifies a date column or a date-time 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 |
Sub Months
Subtracts the specified number of months from the given date value.
| Name | Description |
|---|---|
datetime Datetime | Specifies a date column or a date-time 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 |
Difference in Months
Returns the number of months between startDate and endDate.
| Name | Description |
|---|---|
startDate Datetime | Specifies a date column or a date-time column. |
endDate Datetime | Specifies a date column or a date-time 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 |
Add Days
Adds the specified number of days to the specified date value.
| Name | Description |
|---|---|
date Datetime | Specifies a date column or a date-time 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 |
Sub Days
Subtracts the specified number of days from the specified date value.
| Name | Description |
|---|---|
date Datetime | Specifies a date column or a date-time 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 |
Difference in Days
Returns the number of days between startDate and endDate.
| Name | Description |
|---|---|
startDate Datetime | Specifies a date column or a date-time column. |
endDate Datetime | Specifies a date column or a date-time 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 |
Add Hours
Adds the specified number of hours to the given Time 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 |
Sub Hours
Subtracts the specified number of hours from the given date value.
| Name | Description |
|---|---|
datetime Datetime | Specifies the date-time 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 |
Difference in Hours
Returns the number of hours between startDate timestamp and endDate timestamp.
| Name | Description |
|---|---|
startDateTimestamp Datetime | Specifies a date-time column. |
endDateTimestamp Datetime | Specifies a date-time 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 |
Add Minutes
Adds the specified number of minutes to the given Time or a Datetime value.
| Name | Description |
|---|---|
datetime Datetime | Specifies the Time or a 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 |
Sub Minutes
Subtracts the specified number of minutes from the given date-time value.
| Name | Description |
|---|---|
datetime Datetime | Specifies the date-time 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 |
Difference in Minutes
Returns the number of minutes between startDate timestamp and endDate timestamp.
| Name | Description |
|---|---|
startDateTimestamp Datetime | Specifies a date-time column. |
endDateTimestamp Datetime | Specifies a date-time 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 |
Add Seconds
Adds the specified number of seconds to the given Time or a Datetime value.
| Name | Description |
|---|---|
datetime Datetime | Specifies the Time or a 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 |
Sub Seconds
Subtracts the specified number of seconds in the given date-time value.
| Name | Description |
|---|---|
datetime Datetime | Specifies the date-time 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 |
Difference in Seconds
Returns the number of milliseconds between startDate timestamp and endDate timestamp.
| Name | Description |
|---|---|
startDateTimestamp Datetime | Specifies a date-time column. |
endDateTimestamp Datetime | Specifies a date-time 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 |
Invert Case
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! |
Sentence Case
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" |
Camel Case
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 |
Title Case
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 |
Lines to Space
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 |
Space to Lines
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 |
Tab to Space
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 |
Space to Tab
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 |
To Single Space
Returns a text with multiple spaces into a single 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 Office Suite') | Zoho Office Suite |
To Single Tab
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 |
Remove Empty Lines
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 |
Truncate Character
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 |
Truncate Words
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 |
Remove Space
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" |
To Unix Timestamp
Returns the UNIX timestamp of the given date time values.
| Name | Description |
|---|---|
date Datetime | Specifies a date column or a date-time column. |
| Data column |
|---|
| 06/12/2018 00 03 00 |
| Function | Result |
|---|---|
| to_unix_timestamp(Data column) | 1544034780 |
Is Missing
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] |
Is Valid
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, datetime, decimal, currency, length, temperature, boolean, email, URL, list, map and custom data types |
| Column1 |
|---|
| "ABC" |
| "DEF" |
| "GHI" |
| Function | Result |
|---|---|
| is_valid(`Column1`,'text') | TRUE |
Is Invalid
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, datetime, decimal, currency, length, temperature, boolean, email, URL, list, map and custom data types |
| Column1 |
|---|
| "ABC" |
| "DEF" |
| "GHI" |
| Function | Result |
|---|---|
| is_invalid(`Column1`, 'Number') | TRUE |
Size
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 |
List Concat
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"] |
List Unique
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"] |
List Intersect
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"] |
List Zip
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"]] |
Split
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",""] |
Collect Set
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"] |
Contains
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 |
Starts With
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 |
Ends With
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 |
Extract Date With Format
Returns the date component from date column in the given format.
| Name | Description |
|---|---|
datetime Date | Parameter must be a date column or a date-time 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 |
Extract Time With Format
Returns the time component from datetime column in the given format.
| Name | Description |
|---|---|
datetime Datetime | Parameter is mandatory and it must be a date-time 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 |
Keyword Extraction
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') |
Language Prediction
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 |
Min If
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 |
Max If
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 |
Avg If
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 |
Sum If
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] |
Mode If
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 |
Standard Deviation If
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 |
Variance If
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 |
Count If
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 |
Count Distinct
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 |
Count Distinct If
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 |
Range
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] |
Locate
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 |
Lpad
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 |
Rpad
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 |
Next as List
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 |
| Function | Result |
|---|---|
| nest_as_list('Arjun', 'Zip code: 308-242-0670', 'Status: New resident') | ["Arjun", "Zip code: 99501", "Status: New resident"] |
Nest as Map
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.
| Name | Description |
|---|---|
params Multiple | All parameters must be a column |
| details |
|---|
| ['Name', 'Arjun', 'Zip code', '308-242-0670'] |
| Function | Result |
|---|---|
| nest_as_map(details) | {'Name' : 'Arjun', 'Zip code' : '308-242-0670'} |
ELT
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 Multiple | 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 |
Field
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 Multiple | 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 |
Insert
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 |
Repeat
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 be less than or equal to 9999. |
| Function | Result |
|---|---|
| repeat('Abcd', 3) | AbcdAbcdAbcd |
Percentile
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 |
Acos
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 |
Asin
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 |
Atan
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 |
Atan
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 |
Cos
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 |
Sin
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 |
Tan
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 |
Cot
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 |
Sign
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 true if the given date value is within the previous N days from today
| Name | Description |
|---|---|
date Datetime | Specifies a date column or a date-time 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 date-time column |
num Number | Specifies the number of months |
| Data column |
|---|
| 05/15/2020 |
| Function | Result |
|---|---|
| is_last_nmonth(Data column, 3) | false |
Is Last NQuarter
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 date-time column |
num Number | Specifies the number of quarters |
| Data column |
|---|
| 05/15/2020 |
| Function | Result |
|---|---|
| is_last_nquarter(Data column, 3) | false |
Is Last NYear
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 date-time column |
num Number | Specifies the number of years |
| Data column |
|---|
| 06/15/2019 |
| Function | Result |
|---|---|
| is_last_nyear(Data column, 10) | false |
Is Current Month
Returns true if the given date or the date-time value falls under the current month.
| Name | Description |
|---|---|
date Datetime | Specifies a date column or a date-time column. |
| Data column |
|---|
| 06/15/2020 |
| Function | Result |
|---|---|
| is_current_month(Data column) | false |
Is Current Quarter
Returns true if the given date or the date-time value falls under the current quarter.
| Name | Description |
|---|---|
date Datetime | Specifies a date column or a date-time column. |
| Data column |
|---|
| 07/15/2000 |
| Function | Result |
|---|---|
| is_current_quarter(Data column) | false |
Is Current Week
Returns true if the given date or the date-time 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 date-time 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 |
Is Current Year
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 |
Is Next NDay
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 date-time column |
num Number | Specifies the number of days |
| Data column |
|---|
| 06/18/2020 |
| Function | Result |
|---|---|
| is_next_nday(Data column, 10) | false |
Is Next NMonth
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 date-time column |
num Number | Specifies the number of months |
| Data column |
|---|
| 06/18/2020 |
| Function | Result |
|---|---|
| is_next_nmonth(Data column, 3) | false |
Is Next NQuarter
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 date-time column |
num Number | Specifies the number of quarters |
| Data column |
|---|
| 06/18/2020 |
| Function | Result |
|---|---|
| is_next_nquarter(Data column, 1) | false |
Is Next NYear
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 date-time column |
num Number | Specifies the number of years |
| Data column |
|---|
| 06/15/2020 |
| Function | Result |
|---|---|
| is_next_nyear(Data column, 2) | false |
Start Date
Returns start date for the given date value using the given date units
| Name | Description |
|---|---|
date Datetime | Specifies a date column or a date-time 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 |
End Date
Returns the end date for the given date value using the given date units.
| Name | Description |
|---|---|
date Datetime | Specifies a date column or a date-time 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 |
ASCII
Returns the ASCII value of the specified character
| Name | Description |
|---|---|
string Text | Parameters must be a Text or a Text column, or an expression that returns a Text |
| Function | Result |
|---|---|
| ascii('A') | 65 |
Bit Length
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 |
Index Of
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 |
Space
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') | ' ' |
Unhex
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 |
Tomorrow
Returns the date after the current date
| Function | Result |
|---|---|
| tomorrow(); | 02/06/2020 |
Yesterday
Returns the date of the previous day from the current date
| Function | Result |
|---|---|
| yesterday(); | 28/05/2020 |
Year and Week
Returns the year and week of the given date
| Name | Description |
|---|---|
date Datetime | Parameter must be a date column or a date-time column |
| Data column |
|---|
| 06 Dec 2018 |
| 17 Nov 2018 |
| Function | Result |
|---|---|
| year_week(Data column); | 201848, 201846 |
String Compare
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 |
Correlation
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 |
First Date of the Current Week
Returns the day the current week begins with
| Name | Description |
|---|---|
date Datetime | Specifies a date column or a date-time 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 |
Is Previous Week
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 date-time 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 |
Is Next Week
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 date-time 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 |
Current Time
Returns the current time. Function accepts no parameters.
| Function | Result |
|---|---|
| current_time() | 19:18:55 |
Parse Date
Returns date in the required format if the given value falls under any of the given formats
| Name | Description |
|---|---|
date Text | Specifies a column with dates in different formats |
outputFormat Text | Specifies the date format that the given date should be parsed into |
inputFormats Multiple | Specifies the possible date formats as a list present in the date 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 |
Convert Timezone
Returns the date and time value in converted timezone
| Name | Description |
|---|---|
datetime Datetime | Specifies the date-time 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 |
Seconds To Time
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 |
Remove Symbols
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 |
Substring in-between
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 |
Substring Index
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 |
Substring Index
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 |
Microsecond
Returns the microsecond value from the given date-time value
| Name | Description |
|---|---|
datetime Datetime | Specifies a date time column |
| Data column |
|---|
| 1990-08-07 10 35 23 3427 |
| Function | Result |
|---|---|
| microsecond(Data column) | 342700 |
Make Date
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 |
Add Time
Adds the specified time to the given date-time column.
| Name | Description |
|---|---|
datetime Datetime | Specifies a date-time column. |
time Text | Specifies the time to increase the date-time. |
| Data column |
|---|
| 12/08/2018 07 58 59 |
| Function | Result |
|---|---|
| add_time(Data column, '01:00:00') | 12/08/2018 08:58:59 |
Sub Time
Returns the datetime by subtracting the specified time from the given value with time value
| Name | Description |
|---|---|
datetime Datetime | Specifies a date-time column |
time Text | Specifies the time to decrement from the date-time 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 |
Period Add
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 |
Period Difference
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 |
To Currency
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 |
To Length
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 |
To Temperature
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 |
Financial Year
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 |
Quotient
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 |
Lists to Map
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] |
Collect List
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] |
Keys from Map
Returns the keys from the map passed as the parameter to the function.
| Name | Description |
|---|---|
param Map | Parameter must be a column containing maps. |
| Map Column |
|---|
| key1; value1 |
| key2; value2 |
| key3; value3 |
| Function | Result |
|---|---|
| keys_from_map(mapColumn) | key1, key2, key3 |
To Single Line
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 |
Duration to Years
Returns the number of years from the given duration value.
| Name | Description |
|---|---|
duration Duration | Specifies the Duration column. |
| Data column |
|---|
| 400.10:35:23 |
| Function | Result |
|---|---|
| duration_to_years(Data column) | 1 |
Duration to Months
Returns the number of months from the given duration value.
| Name | Description |
|---|---|
duration Duration | Specifies the Duration column. |
| Data column |
|---|
| 800.10:35:23 |
| Function | Result |
|---|---|
| duration_to_months(Data column) | 26 |
Duration to Weeks
Returns the number of weeks from the given duration value.
| Name | Description |
|---|---|
duration Duration | Specifies the Duration column. |
| Data column |
|---|
| 800.10:35:23 |
| Function | Result |
|---|---|
| duration_to_weeks(Data column) | 114 |
Duration to Days
Returns the number of days from the given duration value.
| Name | Description |
|---|---|
duration Duration | Specifies the Duration column. |
| Data column |
|---|
| 400.10:35:23 |
| Function | Result |
|---|---|
| duration_to_days(Data column) | 400 |
Duration to Hours
Returns the number of hours from the given duration value.
| Name | Description |
|---|---|
duration Duration | Specifies the Duration column. |
| Data column |
|---|
| 800.10:35:23 |
| Function | Result |
|---|---|
| duration_to_hours(Data column) | 19120 |
Duration to Minutes
Returns the number of minutes from the given duration value.
| Name | Description |
|---|---|
duration Duration | Specifies the Duration column. |
| Data column |
|---|
| 400.10:35:23 |
| Function | Result |
|---|---|
| duration_to_minutes(Data column) | 576635 |
Duration to Seconds
Returns the number of seconds from the given duration value.
| Name | Description |
|---|---|
duration Duration | Specifies the Duration column. |
| Data column |
|---|
| 400.10:35:23 |
| Function | Result |
|---|---|
| duration_to_seconds(Data column) | 34598123 |
Add Years to Duration
Returns the duration value by adding the given number of years.
| Name | Description |
|---|---|
duration Duration | 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 |
Add Months to Duration
Returns the duration value by adding the given number of months.
| Name | Description |
|---|---|
duration Duration | 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 |
Add Weeks to Duration
Returns the duration value by adding the given number of weeks.
| Name | Description |
|---|---|
duration Duration | 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 |
Add Days to Duration
Returns the duration value by adding the given number of days.
| Name | Description |
|---|---|
duration Duration | 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 |
Add Hours to Duration
Returns the duration value by adding the given number of hours.
| Name | Description |
|---|---|
duration Duration | 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 |
Add Minutes of Duration
Returns the duration value by adding the given number of minutes.
| Name | Description |
|---|---|
duration Duration | 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 |
Add Seconds to Duration
Returns the duration value by adding the given number of seconds.
| Name | Description |
|---|---|
duration Duration | 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 |
Sub Years from Duration
Returns the duration value by subtracting the given number of years.
| Name | Description |
|---|---|
duration Duration | 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 |
Sub months from duration
Returns the duration value by subtracting the given number of months.
| Name | Description |
|---|---|
duration Duration | 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 |
Sub Weeks from Duration
Returns the duration value by subtracting the given number of weeks.
| Name | Description |
|---|---|
duration Duration | 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 |
Sub Days from Duration
Returns the duration value by subtracting the given number of days.
| Name | Description |
|---|---|
duration Duration | 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 |
Sub Hours from Duration
Returns the duration value by subtracting the given number of hours.
| Name | Description |
|---|---|
duration Duration | 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 |
Sub Minutes from Duration
Returns the duration value by subtracting the given number of minutes.
| Name | Description |
|---|---|
duration Duration | 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 |
Sub Seconds from Duration
Returns the duration value by subtracting the given number of seconds.
| Name | Description |
|---|---|
duration Duration | 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 |
Timestamp Difference in Duration
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 |
Make Duration
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 |
Time to Minute
Returns the total number of minutes of the given time value.
| Name | Description |
|---|---|
time Time | Specifies a Time or a Datetime column. |
| Data column |
|---|
| 11:22:33 |
| Function | Result |
|---|---|
| time_to_minute('Data column) | 682 |
Make Time
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 |
Is Current Hour
Returns true if the given time falls within the current hour, false otherwise.
| Name | Description |
|---|---|
time Time | Specifies a Time or a Datetime column. |
| Data column |
|---|
| 02:35:54 |
| Function | Result |
|---|---|
| is_current_hour(Data column) | false |
Is Last NHour
Returns true if the given hour falls under the last N number of hours from the current hour.
| Name | Description |
|---|---|
time Time | Specifies a Time, Duration 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 |
Is Next NHour
Returns true if the given hour falls under the next N number of hours from the current hour.
| Name | Description |
|---|---|
time Time | Specifies a Time, Duration 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 |
Start of Hour
Returns the starting hour of the given time value.
| Name | Description |
|---|---|
time Time | Specifies a Time, Duration or a Datetime column. |
| Data column |
|---|
| 10:35:23 |
| Function | Result |
|---|---|
| start_of_hour(Data column) | 10:00:00 |
End of Hour
Returns the ending hour of the given time value.
| Name | Description |
|---|---|
time Time | Specifies a Time, Duration or a Datetime column. |
| Data column |
|---|
| 10:35:23 |
| Function | Result |
|---|---|
| end_of_hour(Data column) | 11:00:00 |
Time Sum In Duration
Returns the sum of two time values in the default duration format.
| Name | Description |
|---|---|
time1 Time | Specifies a Time, Duration or a Datetime column. |
time2 Time | Specifies a Time, Duration 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 |
Time Difference in Duration
Returns the difference between two time values in the default duration format.
| Name | Description |
|---|---|
time1 Time | Specifies a Time, Duration or a Datetime column. |
time2 Time | Specifies a Time, Duration 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 |
Operators
Operator | Description |
! | Logical not |
!= | Not equal to |
% | Modulo |
& | Bitwise AND |
* | Multiply |
+ | Add |
- | Subtract |
/ | Divide |
< | Less than |
<= | Less than |
<=> | 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 |
Limitations
- 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.
Zoho CRM Training Programs
Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.
Zoho DataPrep Personalized Demo
If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.
- Zoho Workerly Home
- Forums
- Connect With Us:
- Zoho Recruit Home
- Forums
- Connect With Us:
You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.
Zoho Sheet Resources
Zoho Forms Resources
Zoho Sign Resources
Sign, Paperless!
Zoho TeamInbox Resources
Zoho DataPrep Resources
Design. Discuss. Deliver.
Related Articles
Window functions to add formula columns
A window function is used to perform calculations across a group of rows on a table. This set of rows is termed a window. You can use window functions to perform summations and calculations based on a rolling window of data, relative to the current ...Add formula
You can create new columns and customize them using the Formula column transform. Zoho DataPrep offers a variety of functions to suit your needs. Click here to know more about the functions. To create a formula column 1. Click on the Transform menu ...Window functions to add formula columns
A window function is used to perform calculations across a group of rows on a dataset. A set of rows is termed a window. You can use window functions to perform summations and calculations based on a rolling window of data, relative to the current ...Add prefix or suffix
DataPrep allows you to add prefixes and suffixes to your data. To add a prefix or a suffix to a column 1. Right-click on a column and select the Add Prefix or Suffix option from the context menu. 2. Enter the prefix, suffix, or both in the text ...Create workspace and add new dataset
To import data, you need to create a new workspace, or open an existing workspace and add a dataset. To import data 1. Create a new workspace from the home page using the Create workspace option. Alternatively, you can select an existing workspace if ...