Functions and operators
- Last UpdatedNov 16, 2022
- 3 minute read
Fields can be assigned values, which are the results of expressions or functions. In other words, the resulting value on the right-hand side of an assignment is stored into the field (variable) on the left-hand side. For example:
FIELD(3) = FIELD(1)+FIELD(2)
FIELD(4) = UPPER(FIELD(4))
FIELD(5) = NOW()
The following sections describe the operations you can perform on data in fields. The data types of all operands in the expression on the assignment’s right-hand side are implicitly converted as needed. For example, when two numeric operands are added using a "+" operator, both operands are interpreted as numbers. When the "+" operator is applied to strings, the operands are concatenated. To enforce operator precedence, use parentheses.
For more examples, see the .ini files installed with the interface in the Examples directory, or see Learn about UFL examples.
Arithmetic and logical operators
|
Function |
Meaning |
Data Types |
|---|---|---|
|
* / |
Multiply and Divide. |
Number, Time |
|
+ - |
Add and Subtract. |
Number, DateTime, Time, String |
|
& |
String concatenation. |
String |
|
AND |
Logical AND. Returns 1 if both operands are non-zero, else returns 0. |
Number |
|
OR |
Logical OR. Returns 1 if either operand is non-zero, else returns 0. |
Number |
|
IS NULL |
Checks if a field is a NULL. |
Any |
|
IS NOT NULL |
Checks if a field is not a NULL. |
Any |
Mathematical functions
|
Function |
Description |
Data Types |
|---|---|---|
|
ABS |
Absolute value. |
Number ABS(x Number) |
|
ACOS, ASIN, ATAN, ATAN2 COS, COSH SIN, SINH TAN, TANH |
Trigonometric functions. Angles are in radians. |
Number ACOS(x Number) … NumberATAN2(x Number, y Number) |
|
CEILING |
Rounds a number with a fractional portion to the next higher integer. |
Number CEILING(x Number) |
|
EXP |
Exponential value to base e (Euler's number ≈ 2.71828). |
Number EXP(x Number) |
|
FLOOR |
Largest integer less than or equal to the given numeric expression. |
Number FLOOR(x Number) |
|
LOG |
Logarithm to base e (Euler's number ≈ 2.71828). |
Number LOG(x Number) |
|
LOG10 |
Logarithm to base 10. |
Number LOG10(x Number) |
|
PI |
Mathematical constant. Ratio of a circle's circumference to its diameter, ≈ 3.14159. |
Number PI() |
|
ROUND |
Rounds the value. Numbers that are right in the middle, for example 0.5, are rounded up. |
Number ROUND(x Number) |
String functions
|
Function |
Description |
Data Types |
|---|---|---|
|
CHAR |
Converts an int32 ASCII code (0-255) to a character. |
String CHAR(n Int32) |
|
CONCAT |
Concatenate two strings. |
String CONCAT(x String, y String) |
|
INSTR |
Returns the position of the given occurrence of a specified substring. Positions start with 1. Returns 0 if specified substring is not found. |
Int32 INSTR(x String, substring String, start Int32, occurrence Int32) |
|
LOWER |
All characters lower-case. |
String LOWER(x String) |
|
LEFT |
Returns the leftmost n characters. |
String LEFT(x String, n Int32) |
|
LEN |
Number of characters excluding leading and trailing blanks. |
Int32 LEN(x String) |
|
LTRIM |
Trim the leading blanks. |
String LTRIM (x String) |
|
REPLACE |
Find the specified string and replace it with the third parameter. |
String REPLACE(x String, findWhat String, replaceWith String) For replacing the double quotes with some other (single) character, use the CHAR (34) function instead of typing the " directly: REPLACE (FIELD(2), CHAR(34), " ") |
|
RIGHT |
Returns the rightmost n characters. |
String RIGHT(x String, n Int32) |
|
RTRIM |
Trim the trailing blanks. |
String RTRIM(x String) |
|
SPACE |
Character string consisting of n spaces. |
String SPACE(n Int32) |
|
SUBSTR |
String consisting of LEN characters starting at start position. |
String SUBSTR(x String, start Int, LEN Int) |
|
TRIM |
Trim leading and trailing blanks. |
String TRIM(x String) |
|
UPPER |
All characters upper-case. |
String UPPER (x String) |
DateTime and Time functions
The following functions extract a portion of a DateTime or Time value.
|
Function |
Data Types |
|---|---|
|
DAY |
Int32 DAY(x DateTime) |
|
FRACTION (Extracts the sub-seconds) |
Number FRACTION(x DateTime) |
|
HOUR |
Int32 HOUR(x DateTime) Int32HOUR(x Time) |
|
MINUTE |
Int32 MINUTE(x DateTime) Int32MINUTE(x Time) |
|
MONTH |
Int32 MONTH(x DateTime) |
|
MONTHNAME |
String MONTHNAME(x DateTime) |
|
SECOND |
Int32 SECOND(x DateTime) Int32 SECOND(x Time) |
|
WEEK |
Int32 WEEK(x DateTime) |
|
YEAR |
Int32 YEAR(x DateTime) |