|
Category
|
Operator/Function
|
Type
|
Description
|
Result
|
|
Arithmetic
|
+
|
operator
|
expr1 + expr2
Expr1 plus expr2. Both values must be numeric. If any of values is missing then result is also MISSING
|
number
|
|
|
-
|
operator
|
expr1 - expr2
Expr1 minus expr2. Both values must be numeric. If any of values is missing then result is also MISSING
|
number
|
|
|
*
|
operator
|
expr1 * expr2
Expr1 multiplied by expr2. Both values must be numeric. If any of values is missing then result is also MISSING
|
number
|
|
|
/
|
operator
|
expr1 / expr2
Expr1 divided by expr2. Both values must be numeric. If any of values is missing then result is also MISSING
|
number
|
|
|
^
|
operator
|
expr1 ^ expr2
Expr1 to the power of expr2. Both values must be numeric. If any of values is missing then result is also MISSING
|
number
|
|
|
ABS
|
function
|
ABS(expr1)
Absolute value of expr1. Input value must be numeric. If input value is missing then result is also MISSING
|
number
|
|
Comparison
|
==
|
operator
|
expr1 == expr2
TRUE if the two values are equal. Comparison which involves any MISSING value always returns FALSE. Only values of the same type can be compared against each other. When strings are being compared, the comparison is case sensitive and accent sensitive
|
boolean
|
|
|
!=
|
operator
|
expr1 != expr2
TRUE if the two values are not equal. Comparison which involves any MISSING value always returns FALSE. Only values of the same type can be compared against each other. When strings are being compared, the comparison is case sensitive and accent sensitive
|
boolean
|
|
|
>
|
operator
|
expr1 > expr2
TRUE if first value is greater than the other. Comparison which involves any MISSING value always returns FALSE. When strings are being compared, the comparison is case sensitive and accent sensitive
|
boolean
|
|
|
<
|
operator
|
expr1 < expr2
TRUE if first value is smaller than the other. Comparison which involves any MISSING value always returns FALSE. When strings are being compared, the comparison is case sensitive and accent sensitive
|
boolean
|
|
|
>=
|
operator
|
expr1 >= expr2
TRUE if first value is equal to or greater than the other value. Comparison which involves any MISSING value always returns FALSE. When strings are being compared, the comparison is case sensitive and accent sensitive
|
boolean
|
|
|
<=
|
operator
|
expr1 <= expr2
TRUE if first value is equal to or smaller than the other value. Comparison which involves any MISSING value always returns FALSE. When strings are being compared, the comparison is case sensitive and accent sensitive
|
boolean
|
|
|
IN
|
operator
|
expr1 IN expr2
|
boolean
|
|
|
ISMISSING
|
function
|
ISMISSING(expr1)
Returns TRUE or FALSE depending on whether expr1 evaluates to a MISSING value
|
boolean
|
|
Logical
|
AND
|
operator
|
expr1 AND expr2
TRUE if both expr1 and expr2 are TRUE. Otherwise FALSE
|
boolean
|
|
|
OR
|
operator
|
expr1 OR expr2
TRUE if at least one of expr1, expr2 is TRUE. Otherwise FALSE
|
boolean
|
|
|
NOT
|
operator
|
NOT expr1
TRUE if expr1 is FALSE. Otherwise FALSE
|
boolean
|
|
String
|
UCASE
|
function
|
UCASE(expr1)
Upper case of string
|
string
|
|
|
LCASE
|
function
|
LCASE(expr2)
Lower case of string
|
string
|
|
|
LEFT
|
function
|
LEFT(expr1, expr2)
First expr2 characters of string expr1. Expr2 must be a non-negative number
|
string
|
|
|
RIGHT
|
function
|
RIGHT(expr1, expr2)
Last expr2 characters of string expr1. Expr2 must be a non-negative number
|
string
|
|
|
MID
|
function
|
MID(expr1, expr2, expr3)
Expr3 characters from string expr1, starting at character expr2 (1-based). Expr2 and expr2 must be non-negative numbers
|
string
|
|
|
LENGTH
|
function
|
LENGTH(expr1)
Number of characters in string expr1
|
number
|
|
|
REPLACE
|
function
|
REPLACE(expr1, expr2, expr3)
Returns a string derived from expr1 in which any occurrences of substring expr2 are replaced with string expr3.
All expr1, expr2 and expr3 are expected to be strings
|
string
|
|
|
FIND
|
function
|
FIND(expr1, expr2)
Returns a position (1-based) at which first occurrence of string expr2 is found in string expr1, or 0 if expr2 is not found in expr1.
Expr1 and expr2 must be strings
|
number
|
|
|
SPLIT
|
function
|
SPLIT(expr1, expr2)
Returns a multi-choice value created by splitting string expr1 into pieces separated by string expr2
|
multi-choice string
|
|
|
CONCAT
|
function
|
CONCAT(expr1, expr2)
String created from concatenating strings expr1 and expr2.
|
string
|
|
|
+
|
operator
|
expr1 + expr2
String created from concatenating strings expr1 and expr2.
|
string
|
|
Multi-value
|
COUNT
|
function
|
COUNT(expr1)
Number of values within multi-choice value expr1
|
number
|
|
|
JOIN
|
function
|
JOIN(expr1, expr2)
Returns a string value which is created from concatenating all values within expr1, separated with expr2.
Expr1 is expected to be a multichoice value while expr2 is expected a string
|
string
|
|
Control flow
|
IFELSE
|
function
|
IFELSE(expr1, expr2, expr3)
Returns expr2 if expr1 is TRUE and returns expr3 if expr1 is FALSE or MISSING.
Expr1 must be a logical expression. Expr2 and Expr3 must be of same type
|
typeof(expr2)
|
|
Value
|
TRUE
|
constant
|
TRUE
TRUE value
|
boolean
|
|
|
FALSE
|
constant
|
FALSE
FALSE value
|
boolean
|
|
|
MISSING
|
constant
|
MISSING
MISSING value
|
boolean
|
