Standard Expression Functions
Summary
- This page lists all the Expression Functions shipped by AdapTable that return a single value
- We have divided them into Boolean and Scalar for convenience sake
Most Expression Functions return a single value.
These can be divided into 2 conceptual groups:
- Boolean Expression Functions: return a true / false value
- Scalar Expression Functions: return any single value
Note
All of these functions can be used in any type of Expression
Boolean Expression Functions
These are Expression Functions that return a true / false value.
Hint
The 6 most common Boolean functions are available in shortcut form (e.g.= instead of EQ)
| Function | Shortcut | Example | Returns True If |
|---|---|---|---|
| EQ | = | [col1]=5 or EQ([col1], 5) | Both inputs are equal |
| NEQ | != | [col1]!=5 or NEQ([col1], 5) | Both inputs are NOT equal |
| GT | > | [col1] > 5 or GT([col1], 5) | Input1 > Input2 |
| LT | < | [col1] < 5 or LT([col1], 5) | Input1 < Input2 |
| GTE | >= | [col1] >= 5 or GTE([col1], 5) | Input1 >= Input2 |
| LTE | <= | [col1] <= 5 or LTE([col1], 5) | Input1 <= Input2 |
| AND | [col1] > 5 AND [col2] > 10 | Both statements are true | |
| OR | [col1] > 5 OR [col2] > 10 | Either statement is true | |
| NOT | !([col1] > 5) | Negation of statement is true | |
| BETWEEN | BETWEEN(5, [col1], [col2]) | Input1 between Input2 & Input3 | |
| IN | [col1] IN (5, 10, 17) | Any input value is in inputted column | |
| CONTAINS | CONTAINS([col1], 's' ) | Input1 contains Input2 | |
| STARTS_WITH | STARTS_WITH([col1], 's' ) | Input1 starts with Input2 | |
| ENDS_WITH | ENDS_WITH([col1], 's' ) | Input1 ends with Input2 | |
| ANY_CONTAINS | ANY_CONTAINS('abc') | Any column contains input | |
| IS_BLANK | IS_BLANK([col1]) | Input value is empty | |
| IS_NOT_BLANK | IS_NOT_BLANK([col1]) | Input value is not empty | |
| IS_NUMERIC | IS_NUMERIC([col1]) | Input value is a number | |
| REGEX | REGEX([col1, pattern]) | Input value matches pattern as Regex | |
| IS_HOLIDAY | IS_HOLIDAY([col1) | Input value is a Holiday | |
| IS_WORKDAY | IS_WORKDAY([col1) | Input value is a Working Day |
Scalar Expression Functions
These are Expression Functions that return a single value of any type (though its most commonly numeric).
| Function | Shortcut | Example | Returns |
|---|---|---|---|
| ADD | + | [col1] + 5 or ADD([col1], 5) | Sum of inputted numbers |
| SUB | - | [col1] - 5 or SUB([col1], 5) | Number2 minus Number1 |
| MUL | * | [col1] * 5 or MUL([col1], 5) | Product of inputted numbers |
| DIV | / | [col1] / 5 or DIV([col1], 5) | Division of inputted numbers |
| MOD | % | [col1] % 5 or MOD([col1], 5) | Modulo of 2 numbers |
| POW | ^ | [col1] ^ 5 or POW([col1], 5) | Pow of 2 numbers |
| MIN | MIN([col1], 5) | Smallest of inputted numbers | |
| MAX | MAX([col1], 5) | Highest of inputted numbers | |
| AVG | AVG([col1], 5) | Average of inputted numbers | |
| ABS | ABS([col1]) | Abs value of inputted number | |
| CEILING | CEILING([col1]) | Smallest integer >= inputted number | |
| FLOOR | FLOOR([col1]) | Largest integer <= inputted number | |
| ROUND | ROUND([col1]) | Rounds number to nearest integer | |
| DATE | DATE('20210101') | New Date using input value | |
| NOW | [col1] > NOW() | The current Date | |
| CURRENT_DAY | [col1] > CURRENT_DAY() | The current Day | |
| DAY | DAY([col1]) = DAY(NOW()) | The Day (from a Date) | |
| WEEK | WEEK([col1]) = WEEK(NOW()) | The Week (from a Date) | |
| MONTH | MONTH([col1]) = MONTH(NOW()) | The Month (from a Date) | |
| YEAR | YEAR([col1]) = YEAR(NOW()) | The Year (from a Date) | |
| ADD_DAYS | [col1] < ADD_DAYS(NOW(), 5) | Date using input data & Days to add | |
| ADD_WEEKS | [col1] < ADD_WEEKS(NOW(), 5) | Date using input data & Weeks to add | |
| ADD_MONTHS | [col1] < ADD_MONTHS(NOW(), 5) | Date using input data & Months to add | |
| ADD_YEARS | [col1] < ADD_YEARS(NOW(), 5) | Date using input data & Years to add | |
| DIFF_DAYS | DIFF_DAYS([col], NOW() ) | Difference in Days between 2 Dates | |
| DIFF_WEEKS | DIFF_WEEKS([col], NOW() ) | Difference in Weeks between 2 Dates | |
| DIFF_MONTHS | DIFF_MONTHS([col], NOW() )) | Difference in Months between 2 Dates | |
| DIFF_YEARS | DIFF_YEARS([col], NOW() ) | Difference in Years between 2 Dates | |
| SUB_STRING | SUB_STRING([col1],1,5) | New string extracted from given string | |
| REPLACE | REPLACE([col1],'GBP','EUR') | String with matching chars replaced | |
| COALESCE | COALESCE([col1],[col2],[col3]) | First input value which is not null | |
| LEN | LEN([col1]) | Number of characters in a string | |
| UPPER | UPPER([col1]) | Input string to Upper Case | |
| LOWER | LOWER([col1]) | Input string to Lower Case | |
| CONCAT | CONCAT([col1],[col2],[col3]) | Concatenation of input strings | |
| TO_ARRAY | TO_ARRAY(col1],[col2],[col3]) | Array using the inputted arguments | |
| NULL | NULL | NULL literal, intentional absence of value |