Arithmetic Operators¶
Arithmetic operators are used to generate numeric output from one or more input expressions.
The input expressions must be numeric (both fixedpoint and floating point numbers are allowed), except for the unary operator (+
), which can take a number string, but this will cause the
string to be implicitly converted to its corresponding numeric value.
In this Topic:
List of Arithmetic Operators¶
Operator  Syntax  Description 

+ 
a + b 
Adds two numeric expressions (a and b ). 
+ (unary) 
+a 
Returns a , which will cause implicit conversion of a to a numeric value. If a is a string, but the string cannot be converted to a numeric value, an error is returned. 
 
a  b 
Subtracts one numeric expression (b ) from another (a ). 
 (unary) 
a 
Negates the input numeric expression. 
* 
a * b 
Multiplies two numeric expressions (a and b ). 
/ 
a / b 
Divides one numeric expression (a ) by another (b ). 
% 
a % b 
Computes the modulo of numeric expression a per b . See also MOD. 
Scale and Precision in Arithmetic Operations¶
The scale and precision of the output of an arithmetic operation depends on the scale and precision of the input(s).
This section describes the calculations Snowflake uses to preserve scale and precision in the numeric output generated by various arithmetic operations (multiplication, division, etc.). The following descriptions are used in this section:
Leading digits:  Number of digits ( 

Scale:  Number of digits ( 
Precision:  Total number of digits ( Also:

For outputs, note that these are maximum number of digits; the actual number of digits for any given output may be less.
Multiplication¶
When performing multiplication:
 The number of leading digits in the output is the sum of the leading digits in both inputs.
 Snowflake minimizes potential overflow (due to chained multiplication) by adding the number of digits in the scale of both inputs, up to a maximum threshold of 12 digits, unless either of the inputs has a scale larger than 12, in which case the larger input scale is used as the output scale.
In other words, assuming a multiplication operation with two inputs (L1.S1
and L2.S2
), the maximum number of digits in the output are calculated as follows:
Leading digits: L = L1 + L2
Scale: S = min(S1 + S2, max(S1, S2, 12))
Precision: P = L + S
Note
Snowflake performs integer multiplication for numeric values, so intermediate results may cause some overflow; however, the final output will not overflow.
Examples¶
select 10.01 n1, 1.1 n2, n1 * n2;
++++
 N1  N2  N1 * N2 
++
 10.01  1.1  11.011 
++++
select 10.001 n1, .001 n2, n1 * n2;
++++
 I1  I2  I1 * I2 
++
 10.001  0.001  0.010001 
++++
select .1 n1, .0000000000001 n2, n1 * n2;
++++
 N1  N2  N1 * N2 
++
 0.1  0.0000000000001  0.0000000000000 
++++
Division¶
When performing division:
 The leading digits for the output is the sum of the leading digits of the numerator and the scale of the denominator.
 Snowflake minimizes potential overflow in the output (due to chained division) and loss of scale by adding 6 digits to the scale of the numerator, up to a maximum threshold of 12 digits, unless the scale of the numerator is larger than 12, in which case the numerator scale is used as the output scale.
In other words, assuming a division operation with numerator L1.S1
and denominator L2.S2
, the maximum number of digits in the output are calculated as follows:
Leading digits: L = L1 + S2
Scale: S = max(S1, min(S1 + 6, 12))
Precision: P = L + S
In addition, Snowflake performs integer division with rounding.
Note
Similar to multiplication, intermediate division results may cause some overflow; however, the final output will not overflow.
Examples¶
select 2 n1, 7 n2, n1 / n2;
++++
 N1  N2  N1 / N2 
++
 2  7  0.285714 
++++
select 10.1 n1, 2.1 n2, n1 / n2;
++++
 N1  N2  N1 / N2 
++
 10.1  2.1  4.8095238 
++++
select 10.001 n1, .001 n2, n1 / n2;
++++
 N1  N2  N1 / N2 
++
 10.001  0.001  10001.000000000 
++++
select .1 n1, .0000000000001 n2, n1 / n2;
++++
 N1  N2  N1 / N2 
++
 0.1  0.0000000000001  1000000000000.0000000 
++++
Addition and Subtraction¶
For addition or subtraction:
 The leading digits for the output is the largest number of leading digits of the inputs plus 1 (to preserve carried values).
 The scale for the output is the largest scale of the inputs.
In other words, assuming an addition or subtraction operation has two inputs (L1.S1
and L2.S2
), the maximum number of digits in the output are calculated as follows:
Leading digits: L = max(L1, L2) + 1
Scale: S = max(S1, S2)
Precision: P = L + S
Other Nary Operations¶
For all other arithmetic operations with more than one numeric input, such as modulo (a % b
or MOD):
 The leading digits for the output is the largest number of leading digits of the inputs.
 The scale for the output is the largest scale of the inputs.
In other words, assuming an nary operation with inputs L1.S1
, L2.S2
, etc., the maximum number of digits in the output are calculated as follows:
Leading digits: L = max(L1, L2, ...)
Scale: S = max(S1, S2, ...)
Precision: P = L + S
Unary Operations¶
Unary arithmetic operations have the same output precision and scale as the input precision and scale, except for ROUND, which allows explicitly specifying the output scale.
Bitwise Operations¶
The list of supported bitwise arithmetic operations is available at Conditional Expression Functions.
Note:
 For numeric values, bitwise operations only operate on the leading digits in the input. The output always has a scale of zero.
 For binary bitwise operations, the output has the same number of leading digits as the maximum leading digits in the input.