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" endrue

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" endtrue

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