Operators


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 Arg1 raised to the Arg2 power.

 

Example:

2.5^3

[Value Column]^2

Arg1 < Arg2

Operator which can be a part of an IF or a CASE statement. 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

Operator which can be a part of an IF or a CASE statement. 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

Operator which can be a part of an IF or a CASE statement. 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

Operator which can be a part of an IF or a CASE statement. 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

Operator which can be a part of an IF or a CASE statement. 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" )                                            → false

Case when 2 = 2 then "true" else "false" end     → true

If("Hello" = "hello", "true", "false" )       → false
If("" = null, "true", "false" )                      → (Empty)
If(null = null, "true", "false" )                  → (Empty)

Arg1 <> Arg2

Operator which can be part of an 'IF' or a 'CASE' statement. 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

See also:

Operator Precedence