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 



Adds two numeric expressions ( 


Returns 


Subtracts one numeric expression ( 


Negates the input numeric expression. 


Multiplies two numeric expressions ( 


Divides one numeric expression ( 


Computes the modulo of numeric expression 
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 (
L
) to the left of the decimal point in a numeric value. Scale
Number of digits (
S
) to the right of the decimal point in a numeric value. Precision
Total number of digits (
P
) in a numeric value, calculated as the sum of its leading digits and scale (i.e.P = L + S
). Note that precision in Snowflake is always limited to 38.Also:
Fixedpoint data types (NUMBER, DECIMAL, etc.) utilize precision and scale. For example, for the DECIMAL(8,2) data type, precision is 8, scale is 2, and leading digits is 6.
Floating point data types (FLOAT, DOUBLE, REAL, etc.) utilize 8byte doubles.
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.