Math Functions and Operators
supports addition, subtraction, multiplication, and division between any two numeric types. Division involving two integral types always results in an integral type.
These rules determine the precision p and scale s for the result of a specified expression that involves decimals d1 and d2, with precisions p1 and p2, and scales s1 and s2:
Operation | Precision | Scale |
|---|---|---|
addition, subtraction | max(s1, s2) + max(p1-s1, p2-s2) + 1 | max(s1, s2) |
multiplication | p1 + p2 | s1 + s2 |
division | See note. | max(6, s1 + p2 + 1) |
For division, the resulting precision is the minimum necessary to hold the whole number portion of the result. If that portion does not exist because the absolute value of the result is less than one, the resulting precision is one greater than the scale. For other operations, the result might still have a lower precision (but the same scale) than specified by these rules when the result would otherwise have a leading insignificant zero.
If the resulting precision exceeds 31, the database decreases both precision and scale until the precision is 31, except in division where the minimal scale is always six. If the resulting precision still exceeds 31, the database truncates it to 31. The database returns an overflow error when the result exceeds the available integral portion of the result. When the database decreases the resulting scale, it truncates the excess fractional digits in the result.
These scalar math functions operate on a single value and return a single value. They can appear anywhere where an ordinary value or condition can appear.
For a list of common math symbols supported in , see Math Operators.
Returns the absolute value of a specified floating-point number.
Syntax
Example
Output: 250.5
Returns the inverse cosine of a specified floating-point number.
Syntax
Example
Output: 1.318116071652818
Returns the hyperbolic arc-cosine of a specified floating-point number.
Syntax
Example
Output: 1.3169578969248166
Returns the inverse sine of a specified floating-point number.
Syntax
Example
Output: 0.25268025514207865
Returns the hyperbolic arc-sine of a specified floating-point number.
Syntax
Example
Output: 1.4436354751788103
Returns the inverse tangent of a specified floating-point number.
Syntax
Example
Output: 0.24497866312686414
Returns the inverse tangent of two numeric, floating-point values.
Syntax
Example
Output: 0.4636476090008061
Returns the hyperbolic arc-tangent of a specified floating-point number.
Syntax
Example
Output: 0.25541281188299536
The cumulative distribution function of the standard bivariate normal distribution, which returns the probability that a random sample of (A, B) is less than x and less than y (A <= x and B <= y). The correlation is set to 0 in the case where you specify two arguments only.
You can specify an optional rho coefficient to calculate the correlation.
For details, see the bivariate cumulative distribution function.
Syntax
Example
Output: 0.8222040420815764
The probability density function of the standard bivariate normal distribution, which returns the relative likelihood that a random sample would equal the specified values. The correlation is set to 0 in the case where you specify two arguments only.
You can specify an optional rho coefficient to calculate the correlation.
For details, see the bivariate probability density function.
Syntax
Example
Output: 0.013064233284684893
Performs a variety of bit operations.
The char_func can be any of these string literals: 'AND', 'OR', or 'XOR'. This argument determines the bitwise operation that is performed on arguments x and y.
Syntax
Argument | Data Type | Description |
|---|---|---|
character_func | CHAR | This argument determines the bitwise operation that is performed on arguments x and y. Can be any of these string literals: 'AND', 'OR', or 'XOR'. To perform bit operations using a NOT operator, use the BITNOT function. |
x | INT | A numeric value to be evaluated by the function. |
y | INT | A numeric value to be evaluated by the function. |
Example
Output: 8
Example
Output: 15
Example
Output: 7
Alias for the BITFUNC syntax BITFUNC('AND', x, y).
Returns the bitwise negation of integral_x.
Syntax
Example
Output: -2
Alias for the BITFUNC syntax BITFUNC('OR', x, y).
Alias for the BITFUNC syntax BITFUNC('XOR', x, y).
Performs a Boolean logical evaluation on arguments x and y. Numbers equal to 0 evaluate to false, and numbers not equal to 0 evaluate to true.
Syntax
Argument | Data Type | Description |
|---|---|---|
char_func | CHAR | This argument determines the Boolean operation that is performed on arguments x and y. Can be any of these string literals: 'AND', 'OR', or 'XOR'. To perform operations using a NOT operator, use the BOOLNOT function. |
x | INT | A numeric value for evaluation by the function. |
y | INT | A numeric value for evaluation by the function. |
Example
Output: false
Example
Output: true
Example
Output: true
Alias for the BOOLFUNC syntax BOOLFUNC('AND', x, y).
Returns the logical negation of the BOOLFUNC function. Numbers equal to 0 evaluate to true, and numbers not equal to 0 evaluate to false.
Syntax
Example
Output: true
Alias for the BOOLFUNC syntax BOOLFUNC('OR', x, y).
Alias for the BOOLFUNC syntax BOOLFUNC('XOR', x, y).
Returns the cube root of the numeric value x.
Syntax
Example
Output: 4.0
The cumulative distribution function of the standard normal distribution. Returns the probability that a random sample is less than or equal to the specified value.
For details on the cumulative distribution function, read the description on Wikipedia.
Syntax
Example
Output: 0.9331927987311419
Returns the nearest integer greater than or equal to x.
Syntax
Example
Output: 24.0
Alias for CEIL.
Returns the cosine of x.
Syntax
Example
Output: -0.4161468365471424
Returns the hyperbolic cosine of x.
Syntax
Example
Output: 3.7621956910836314
Returns the cotangent of x.
Syntax
Example
Output: -0.45765755436028577
Returns the corresponding angle in degrees for x in radians.
Syntax
Example
Output: 85.94366926962348
Returns the result of x divided by y. If y is zero, returns NULL.
Syntax
Example
Output: 5
The error function is used for measurements that follow a normal distribution. Use this function to find the probability that the error of single measurement lies between [-a, a]. For more information on the error function, read the description on Wikipedia.
Syntax
Example
Output: 1.0
The complement of the error function. ERFC(x) = 1 - ERF(x).
Syntax
Example
Output: 1.1224297172982928E-29
Returns the exponential of x (e raised to the power of x).
Syntax
Example
Output: 2.718281828459045
Returns the nearest integer less than or equal to x.
Syntax
Example
Output: 22.0
For information on the gamma function, read the description on Wikipedia.
Syntax
Example
Output: 40319.99999999999
Returns the x-coordinate of the center of the nearest hexagonal bin to the point (x, y). Hexagonal bins have a side length of 1.
Syntax
Example
Output: 4.5
Returns the y-coordinate of the center of the nearest hexagonal bin to the point (x, y). Hexagonal bins have a side length of 1.
Syntax
Example
Output: 7.794
The inverse of the ERF error function.
Syntax
Example
Output: 0.0
The inverse of the complement of the error function.
Syntax
Example
Output: 0.0
Returns the leaky rectified linear unit function of x.
If unspecified, the optional numeric_value_slope argument defaults to 0.01.
Syntax
Example
Output: -0.4
Returns x shifted to the left by y bits. If y is less than 0, then x shifts to the right by the absolute value. The function throws an out-of-range error if you attempt a shift into the sign bit.
Syntax
Example
Output: 4
Returns the natural logarithm of x (base e or 2.171828…).
Syntax
Example
Output: 0.6931471805599453
Returns the base 2 logarithm of x.
Syntax
Example
Output: 1.0
Returns the base 10 logarithm of x.
The optional base argument specifies the numeral system to use. If unspecified, the function defaults to base 10.
Syntax
Example
Output: 0.3010299956639812
The natural logarithm of the absolute value of the gamma function.
Syntax
Example
Output: 12.80182748008147
Returns the remainder from x divided by y.
Syntax
Example
Output: 2
Returns the constant value of π.
Syntax
Example
Output: 3.141592653589793
Returns the smallest non-negative equivalence class of x % y.
Syntax
Example
Output: 2
Returns x raised to the power of y. The return type FLOAT is returned for a FLOAT input, DECIMAL for DOUBLE input, and BIGINT otherwise.
Syntax
Example
Output: 16
The inverse of the cumulative distribution function. The function accepts only decimal values between 0 and 1.
For more details on the probit function, read the description on Wikipedia.
Syntax
Example
Output: -0.2533471031357998
Returns the corresponding angle in radians for x in degrees.
Syntax
Example
Output: 0.03490658503988659
Takes no argument and returns a random DOUBLE value in the range [0, 1).
Syntax
Example
Output: 0.6053105024272647
Returns the rectified linear unit function of x.
Syntax
Example
Output: 0.0
Returns x shifted to the right by y bits. If y is less than 0, then x shifts to the left by the absolute value of y in bits. The function throws an out-of-range error if you attempt a shift into the sign bit.
Syntax
Example
Output: 2
Returns x rounded to the nearest integer.
Syntax
Example
Output: 12.0
Returns the positive (+1), zero (0), or negative (-1) sign of x.
Syntax
Example
Output: 1
Example
Output: 0
Example
Output: -1
Returns the sine of x.
Syntax
Example
Output: 0.9092974268256817
Returns the hyperbolic sine of x.
Syntax
Example
Output: 3.626860407847019
Returns the square root of x.
Syntax
Example
Output: 9
Returns the square of x.
Syntax
Example
Output: 49
Returns the tangent of x.
Syntax
Example
Output: -5.52037992250933
Returns the hyperbolic tangent of x.
Syntax
Example
Output: 0.9413755384972874
Returns x truncated to y decimal places.
Syntax
Example
Output: 1.234
Alias for TRUNC.
Data Types
Query Ocient