SQL Reference

Math Functions and Operators

Math Operators



 supports addition, subtraction, multiplication, and division between any two numeric types. Division involving two integral types always results in an integral type.

Decimal Precision

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.

Additional Supported Data Type Combinations

Addition

Text


Subtraction

Text


Multiplication

Text


Division

Text


Math Functions

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.

ABS

Returns the absolute value of a specified floating-point number.

Syntax

SQL


Example

SQL


Output: 250.5

ACOS

Returns the inverse cosine of a specified floating-point number.

Syntax

SQL


Example

SQL


Output: 1.318116071652818

ACOSH

Returns the hyperbolic arc-cosine of a specified floating-point number.

Syntax

SQL


Example

SQL


Output: 1.3169578969248166

ASIN

Returns the inverse sine of a specified floating-point number.

Syntax

SQL


Example

SQL


Output: 0.25268025514207865

ASINH

Returns the hyperbolic arc-sine of a specified floating-point number.

Syntax

SQL


Example

SQL


Output: 1.4436354751788103

ATAN

Returns the inverse tangent of a specified floating-point number.

Syntax

SQL


Example

SQL


Output: 0.24497866312686414

ATAN2

Returns the inverse tangent of two numeric, floating-point values.

Syntax

SQL


Example

SQL


Output: 0.4636476090008061

ATANH

Returns the hyperbolic arc-tangent of a specified floating-point number.

Syntax

SQL


Example

SQL


Output: 0.25541281188299536

BICDF

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.

Syntax

SQL


Example

SQL


Output: 0.8222040420815764

BIPDF

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.

Syntax

SQL


Example

SQL


Output: 0.013064233284684893

BITFUNC

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

SQL


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

SQL


Output: 8

Example

SQL


Output: 15

Example

SQL


Output: 7

BITAND

Alias for the BITFUNC syntax BITFUNC('AND', x, y).

BITNOT

Returns the bitwise negation of integral_x.

Syntax

SQL


Example

SQL


Output: -2

BITOR

Alias for the BITFUNC syntax BITFUNC('OR', x, y).

BITXOR

Alias for the BITFUNC syntax BITFUNC('XOR', x, y).

BOOLFUNC

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

SQL


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

SQL


Output: false

Example

SQL


Output: true

Example

SQL


Output: true

BOOLAND

Alias for the BOOLFUNC syntax BOOLFUNC('AND', x, y).

BOOLNOT

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

SQL


Example

SQL


Output: true

BOOLOR

Alias for the BOOLFUNC syntax BOOLFUNC('OR', x, y).

BOOLXOR

Alias for the BOOLFUNC syntax BOOLFUNC('XOR', x, y).

CBRT

Returns the cube root of the numeric value x.

Syntax

SQL


Example

SQL


Output: 4.0

CDF

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

SQL


Example

SQL


Output: 0.9331927987311419

CEIL

Returns the nearest integer greater than or equal to x.

Syntax

SQL


Example

SQL


Output: 24.0

CEILING

Alias for CEIL.

COS

Returns the cosine of x.

Syntax

SQL


Example

SQL


Output: -0.4161468365471424

COSH

Returns the hyperbolic cosine of x.

Syntax

SQL


Example

SQL


Output: 3.7621956910836314

COT

Returns the cotangent of x.

Syntax

SQL


Example

SQL


Output: -0.45765755436028577

DEGREES

Returns the corresponding angle in degrees for x in radians.

Syntax

SQL


Example

SQL


Output: 85.94366926962348

DIV

Returns the result of x divided by y. If y is zero, returns NULL.

Syntax

SQL


Example

SQL


Output: 5

ERF

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

SQL


Example

SQL


Output: 1.0

ERFC

The complement of the error function. ERFC(x) = 1 - ERF(x).

Syntax

SQL


Example

SQL


Output: 1.1224297172982928E-29

EXP

Returns the exponential of x (e raised to the power of x).

Syntax

SQL


Example

SQL


Output: 2.718281828459045

FLOOR

Returns the nearest integer less than or equal to x.

Syntax

SQL


Example

SQL


Output: 22.0

GAMMA

For information on the gamma function, read the description on Wikipedia.

Syntax

SQL


Example

SQL


Output: 40319.99999999999

HEXBINX

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

SQL


Example

SQL


Output: 4.5

HEXBINY

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

SQL


Example

SQL


Output: 7.794

IERF

The inverse of the ERF error function.

Syntax

SQL


Example

SQL


Output: 0.0

IERFC

The inverse of the complement of the error function.

Syntax

SQL


Example

SQL


Output: 0.0

LEAKYRELU

Returns the leaky rectified linear unit function of x.

If unspecified, the optional numeric_value_slope argument defaults to 0.01.

Syntax

SQL


Example

SQL


Output: -0.4

LEFT_SHIFT

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

SQL


Example

SQL


Output: 4

LN

Returns the natural logarithm of x (base e or 2.171828…).

Syntax

SQL


Example

SQL


Output: 0.6931471805599453

LOG2

Returns the base 2 logarithm of x.

Syntax

SQL


Example

SQL


Output: 1.0

LOG

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

SQL


Example

SQL


Output: 0.3010299956639812

LOG_GAMMA

The natural logarithm of the absolute value of the gamma function.

Syntax

SQL


Example

SQL


Output: 12.80182748008147

MOD

Returns the remainder from x divided by y.

Syntax

SQL


Example

SQL


Output: 2

PI

Returns the constant value of π.

Syntax

SQL


Example

SQL


Output: 3.141592653589793

PMOD

Returns the smallest non-negative equivalence class of x % y.

Syntax

SQL


Example

SQL


Output: 2

POWER

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

SQL


Example

SQL


Output: 16

PROBIT

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

SQL


Example

SQL


Output: -0.2533471031357998

RADIANS

Returns the corresponding angle in radians for x in degrees.

Syntax

SQL


Example

SQL


Output: 0.03490658503988659

RAND

Takes no argument and returns a random DOUBLE value in the range [0, 1).

Syntax

SQL


Example

SQL


Output: 0.6053105024272647

RELU

Returns the rectified linear unit function of x.

Syntax

SQL


Example

SQL


Output: 0.0

RIGHT_SHIFT

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

SQL


Example

SQL


Output: 2

ROUND

Returns x rounded to the nearest integer.

Syntax

SQL


Example

SQL


Output: 12.0

SIGN

Returns the positive (+1), zero (0), or negative (-1) sign of x.

Syntax

SQL


Example

SQL


Output: 1

Example

SQL


Output: 0

Example

SQL


Output: -1

SIN

Returns the sine of x.

Syntax

SQL


Example

SQL


Output: 0.9092974268256817

SINH

Returns the hyperbolic sine of x.

Syntax

SQL


Example

SQL


Output: 3.626860407847019

SQRT

Returns the square root of x.

Syntax

SQL


Example

SQL


Output: 9

SQUARE

Returns the square of x.

Syntax

SQL


Example

SQL


Output: 49

TAN

Returns the tangent of x.

Syntax

SQL


Example

SQL


Output: -5.52037992250933

TANH

Returns the hyperbolic tangent of x.

Syntax

SQL


Example

SQL


Output: 0.9413755384972874

TRUNC

Returns x truncated to y decimal places.

Syntax

SQL


Example

SQL


Output: 1.234

TRUNCATE

Alias for TRUNC.

Related Links

Data Types

Query Ocient