Operators
A list of the operators you can use in expressions.
| Operator | Description |
|---|---|
| - Arg1 | Negates the argument. The argument and the result are of type Real. |
| Arg1 + Arg2 | Adds the two arguments. See operator & for String concatenation. |
| Arg1 - Arg2 | Subtracts Arg2 from Arg1. |
| Arg1 * Arg2 | Multiplies the two arguments. The arguments and the result are of type Real or Decimal. |
| Arg1 / Arg2 | Divides
Arg1 by
Arg2. The arguments and the result are of type
Real or
Decimal. Division by zero results in an invalid value.
Examples: 7/2 → 3.5 0/0 → (Empty) -1/0 → (Empty) |
| Arg1 & Arg2 | Appends
Arg2 to the end of
Arg1. The arguments can be of any type, but are converted to strings. The result is of type
String. See also function
Concatenate.
Examples: "April " & (20+1) & "st" → "April 21st" null & "Ape" → (Empty) |
| Arg1 % Arg2 | Returns the remainder of the division of
Arg1 by
Arg2. The arguments and the result are of type
Real or
Decimal. Invalid values are propagated to the result column.
Example: 3.5 % 2.5 → 1.00 |
| Arg1 != Arg2 |
Returns true if Arg1 is not equal to Arg2. Examples: If( 1 != 2, true, false ) Case when 2 != 2 then true else false end |
| Arg1^Arg2 | Returns
Arg1 raised to the
Arg2 power.
Example: 2.5^3 [Value Column]^2 |
| Arg1 < Arg2 | Returns
true if
Arg1 is less than
Arg2. The arguments can be of any type, but must both be of the same type. The result is of type
Boolean. If any argument is invalid, the result is invalid. The function is defined for comparing normal numbers to each other. Other combinations result in invalid values.
Examples: If( 1 < 2, "true", "false" ) → true Case when 2 < 1 then "true" else "false" end → false If(1<null, "true", "false") → (Empty) If(1 < 1/0, "true", "false") → (Empty) |
| Arg1 > Arg2 | Returns true if Arg1 is greater than Arg2. The arguments are of type Real and the result is of type Boolean. See operator < for the definition of valid arguments. |
| Arg1 <= Arg2 | Returns true if Arg1 is less than or equal to Arg2. The arguments are of type Real and the result is of type Boolean. See operator < for the definition of valid arguments. |
| Arg1 >= Arg2 | Returns true if Arg1 is greater than or equal to Arg2. The arguments are of type Real and the result is of type Boolean. See operator < for the definition of valid arguments. |
| Arg1 = Arg2 | Returns true if
Arg1 is equal to
Arg2. The arguments can be of any type, but must both be of the same type. The result is of type
Boolean. If any argument is null, the result is null. For arguments of type
Real, see operator
< for the definition of valid arguments.
Examples: If(1 = 2, "true", "false" ) → alse Case when 2 = 2 then "true" else "false" end → rue If("Hello" = "hello", "true", "false" ) → alse If("" = null, "true", "false" ) → (Empty) If(null = null, "true", "false" ) → (Empty) |
| Arg1 <=> Arg2 |
Returns true if the first argument is equal to the second argument or if both arguments are null. The arguments can be of any type, but must both be of the same type. The result is of type boolean. Example: Case when [Column1] <=> [Column2] then 'match' else 'no match' end |
| Arg1 <> Arg2 | Returns true if Arg1 is not equal to Arg2. The arguments can be of any type, but must both be of the same type. The result is of type Boolean. If any argument is invalid, the result is invalid. For arguments of type Real, see operator <for the definition of valid arguments. |
| Arg1 ~= Arg2 | Operator which can be part of an
IF or a
CASE statement. The arguments can be of any type, but will be treated as string columns. Returns
true if the
Arg2 regular expression string matches the
Arg1 string.
Some characters, like for instance the backslash character "\", need to be escaped to work when using calculated columns. See literature about regular expression language elements, e.g., on MSDN, for more information. Examples: If( "aab" ~= "a+" , "true", "false" ) → true Case when "aba" ~= ".a+$" then "true" else "false" end → true |
| And(Arg1, ...) | Operator which can be part of an
IF or
CASE statement. It has two boolean expressions as arguments and returns
true if both expressions are true.
Examples: If( 1 < 2 and 2 < 3, "true", "false" ) Case when false and true then "true" else "false" end |
| Not(Arg1) | Operator which can be part of an
IF or
CASE statement. It negates the boolean expression given as argument.
Examples: If( not 1 < 2, "true", "false" ) Case when not true then "true" else "false" end |
| Or(Arg1, ...) | Operator which can be part of an
IF or
CASE statement. It has two boolean expressions as arguments and returns
true if one of the expressions is true.
Examples: If( 1 < 2 or 2 < 3, "true", "false" ) Case when false or true then "true" else "false" end |
| Xor(Arg1, ...) | Can be part of an
IF or
CASE statement. It has two boolean expressions as arguments and returns
true if exactly one of the expressions is true.
Examples: If(1 < 2 xor 2 < 3, true, false) Case when [A]>10 xor [B]>5 then 1 else 0 end |