# Mathematical functions in Google Standard SQL

All mathematical functions have the following behaviors:

• They return `NULL` if any of the input parameters is `NULL`.
• They return `NaN` if any of the arguments is `NaN`.

### ABS

``````ABS(X)
``````

Description

Computes absolute value. Returns an error if the argument is an integer and the output value cannot be represented as the same type; this happens only for the largest negative input value, which has no positive representation.

X ABS(X)
25 25
-25 25
`+inf` `+inf`
`-inf` `+inf`

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTINT64NUMERICFLOAT64

### SIGN

``````SIGN(X)
``````

Description

Returns `-1`, `0`, or `+1` for negative, zero and positive arguments respectively. For floating point arguments, this function does not distinguish between positive and negative zero.

X SIGN(X)
25 +1
0 0
-25 -1
NaN NaN

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTINT64NUMERICFLOAT64

### IS_INF

``````IS_INF(X)
``````

Description

Returns `TRUE` if the value is positive or negative infinity.

Returns `FALSE` for `NUMERIC` inputs since `NUMERIC` cannot be `INF`.

X IS_INF(X)
`+inf` `TRUE`
`-inf` `TRUE`
25 `FALSE`

### IS_NAN

``````IS_NAN(X)
``````

Description

Returns `TRUE` if the value is a `NaN` value.

Returns `FALSE` for `NUMERIC` inputs since `NUMERIC` cannot be `NaN`.

X IS_NAN(X)
`NaN` `TRUE`
25 `FALSE`

### IEEE_DIVIDE

``````IEEE_DIVIDE(X, Y)
``````

Description

Divides X by Y; this function never fails. Returns `FLOAT64`. Unlike the division operator (/), this function does not generate errors for division by zero or overflow.

X Y IEEE_DIVIDE(X, Y)
20.0 4.0 5.0
0.0 25.0 0.0
25.0 0.0 `+inf`
-25.0 0.0 `-inf`
0.0 0.0 `NaN`
0.0 `NaN` `NaN`
`NaN` 0.0 `NaN`
`+inf` `+inf` `NaN`
`-inf` `-inf` `NaN`

### SQRT

``````SQRT(X)
``````

Description

Computes the square root of X. Generates an error if X is less than 0.

X SQRT(X)
`25.0` `5.0`
`+inf` `+inf`
`X < 0` Error

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

### POW

``````POW(X, Y)
``````

Description

Returns the value of X raised to the power of Y. If the result underflows and is not representable, then the function returns a value of zero.

X Y POW(X, Y)
2.0 3.0 8.0
1.0 Any value including `NaN` 1.0
Any value including `NaN` 0 1.0
-1.0 `+inf` 1.0
-1.0 `-inf` 1.0
ABS(X) < 1 `-inf` `+inf`
ABS(X) > 1 `-inf` 0.0
ABS(X) < 1 `+inf` 0.0
ABS(X) > 1 `+inf` `+inf`
`-inf` Y < 0 0.0
`-inf` Y > 0 `-inf` if Y is an odd integer, `+inf` otherwise
`+inf` Y < 0 0
`+inf` Y > 0 `+inf`
Finite value < 0 Non-integer Error
0 Finite value < 0 Error

Return Data Type

The return data type is determined by the argument types with the following table.

INPUTINT64NUMERICFLOAT64
INT64FLOAT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

### POWER

``````POWER(X, Y)
``````

Description

Synonym of `POW(X, Y)`.

### EXP

``````EXP(X)
``````

Description

Computes e to the power of X, also called the natural exponential function. If the result underflows, this function returns a zero. Generates an error if the result overflows.

X EXP(X)
0.0 1.0
`+inf` `+inf`
`-inf` 0.0

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

### LN

``````LN(X)
``````

Description

Computes the natural logarithm of X. Generates an error if X is less than or equal to zero.

X LN(X)
1.0 0.0
`+inf` `+inf`
`X < 0` Error

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

### LOG

``````LOG(X [, Y])
``````

Description

If only X is present, `LOG` is a synonym of `LN`. If Y is also present, `LOG` computes the logarithm of X to base Y.

X Y LOG(X, Y)
100.0 10.0 2.0
`-inf` Any value `NaN`
Any value `+inf` `NaN`
`+inf` 0.0 < Y < 1.0 `-inf`
`+inf` Y > 1.0 `+inf`
X <= 0 Any value Error
Any value Y <= 0 Error
Any value 1.0 Error

Return Data Type

INPUTINT64NUMERICFLOAT64
INT64FLOAT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

### LOG10

``````LOG10(X)
``````

Description

Similar to `LOG`, but computes logarithm to base 10.

X LOG10(X)
100.0 2.0
`-inf` `NaN`
`+inf` `NaN`
X <= 0 Error

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

### GREATEST

``````GREATEST(X1,...,XN)
``````

Description

Returns the largest value among X1,...,XN according to the < comparison. If any parts of X1,...,XN are `NULL`, the return value is `NULL`.

X1,...,XN GREATEST(X1,...,XN)
3,5,1 5

Return Data Types

Data type of the input values.

### LEAST

``````LEAST(X1,...,XN)
``````

Description

Returns the smallest value among X1,...,XN according to the > comparison. If any parts of X1,...,XN are `NULL`, the return value is `NULL`.

X1,...,XN LEAST(X1,...,XN)
3,5,1 1

Return Data Types

Data type of the input values.

### DIV

``````DIV(X, Y)
``````

Description

Returns the result of integer division of X by Y. Division by zero returns an error. Division by -1 may overflow. If both inputs are `NUMERIC` and the result is overflow, then it returns a `numeric overflow` error.

X Y DIV(X, Y)
20 4 5
0 20 0
20 0 Error

Return Data Type

The return data type is determined by the argument types with the following table.

INPUTINT64NUMERIC
INT64INT64NUMERIC
NUMERICNUMERICNUMERIC

### SAFE_DIVIDE

``````SAFE_DIVIDE(X, Y)
``````

Description

Equivalent to the division operator (`X / Y`), but returns `NULL` if an error occurs, such as a division by zero error.

XYSAFE_DIVIDE(X, Y)
2045
020`0`
200`NULL`

Return Data Type

INPUTINT64NUMERICFLOAT64
INT64FLOAT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

### SAFE_MULTIPLY

``````SAFE_MULTIPLY(X, Y)
``````

Description

Equivalent to the multiplication operator (`*`), but returns `NULL` if overflow occurs.

XYSAFE_MULTIPLY(X, Y)
20480

Return Data Type

INPUTINT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

### SAFE_NEGATE

``````SAFE_NEGATE(X)
``````

Description

Equivalent to the unary minus operator (`-`), but returns `NULL` if overflow occurs.

XSAFE_NEGATE(X)
+1-1
-1+1
00

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTINT64NUMERICFLOAT64

``````SAFE_ADD(X, Y)
``````

Description

Equivalent to the addition operator (`+`), but returns `NULL` if overflow occurs.

549

Return Data Type

INPUTINT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

### SAFE_SUBTRACT

``````SAFE_SUBTRACT(X, Y)
``````

Description

Returns the result of Y subtracted from X. Equivalent to the subtraction operator (`-`), but returns `NULL` if overflow occurs.

XYSAFE_SUBTRACT(X, Y)
541

Return Data Type

INPUTINT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

### MOD

``````MOD(X, Y)
``````

Description

Modulo function: returns the remainder of the division of X by Y. Returned value has the same sign as X. An error is generated if Y is 0.

X Y MOD(X, Y)
25 12 1
25 0 Error

Return Data Type

The return data type is determined by the argument types with the following table.

INPUTINT64NUMERIC
INT64INT64NUMERIC
NUMERICNUMERICNUMERIC

### ROUND

``````ROUND(X [, N])
``````

Description

If only X is present, `ROUND` rounds X to the nearest integer. If N is present, `ROUND` rounds X to N decimal places after the decimal point. If N is negative, `ROUND` will round off digits to the left of the decimal point. Rounds halfway cases away from zero. Generates an error if overflow occurs.

X ROUND(X)
2.0 2.0
2.3 2.0
2.8 3.0
2.5 3.0
-2.3 -2.0
-2.8 -3.0
-2.5 -3.0
0 0
`+inf` `+inf`
`-inf` `-inf`
`NaN` `NaN`

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

### TRUNC

``````TRUNC(X [, N])
``````

Description

If only X is present, `TRUNC` rounds X to the nearest integer whose absolute value is not greater than the absolute value of X. If N is also present, `TRUNC` behaves like `ROUND(X, N)`, but always rounds towards zero and never overflows.

X TRUNC(X)
2.0 2.0
2.3 2.0
2.8 2.0
2.5 2.0
-2.3 -2.0
-2.8 -2.0
-2.5 -2.0
0 0
`+inf` `+inf`
`-inf` `-inf`
`NaN` `NaN`

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

### CEIL

``````CEIL(X)
``````

Description

Returns the smallest integral value that is not less than X.

X CEIL(X)
2.0 2.0
2.3 3.0
2.8 3.0
2.5 3.0
-2.3 -2.0
-2.8 -2.0
-2.5 -2.0
0 0
`+inf` `+inf`
`-inf` `-inf`
`NaN` `NaN`

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

### CEILING

``````CEILING(X)
``````

Description

Synonym of CEIL(X)

### FLOOR

``````FLOOR(X)
``````

Description

Returns the largest integral value that is not greater than X.

X FLOOR(X)
2.0 2.0
2.3 2.0
2.8 2.0
2.5 2.0
-2.3 -3.0
-2.8 -3.0
-2.5 -3.0
0 0
`+inf` `+inf`
`-inf` `-inf`
`NaN` `NaN`

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

### COS

``````COS(X)
``````

Description

Computes the cosine of X where X is specified in radians. Never fails.

X COS(X)
`+inf` `NaN`
`-inf` `NaN`
`NaN` `NaN`

### COSH

``````COSH(X)
``````

Description

Computes the hyperbolic cosine of X where X is specified in radians. Generates an error if overflow occurs.

If X is `NUMERIC` then, the output is `FLOAT64`.

X COSH(X)
`+inf` `+inf`
`-inf` `+inf`
`NaN` `NaN`

### ACOS

``````ACOS(X)
``````

Description

Computes the principal value of the inverse cosine of X. The return value is in the range [0,π]. Generates an error if X is a value outside of the range [-1, 1].

If X is `NUMERIC` then, the output is `FLOAT64`.

X ACOS(X)
`+inf` `NaN`
`-inf` `NaN`
`NaN` `NaN`
X < -1 Error
X > 1 Error

### ACOSH

``````ACOSH(X)
``````

Description

Computes the inverse hyperbolic cosine of X. Generates an error if X is a value less than 1.

If X is `NUMERIC` then, the output is `FLOAT64`.

X ACOSH(X)
`+inf` `+inf`
`-inf` `NaN`
`NaN` `NaN`
X < 1 Error

### SIN

``````SIN(X)
``````

Description

Computes the sine of X where X is specified in radians. Never fails.

X SIN(X)
`+inf` `NaN`
`-inf` `NaN`
`NaN` `NaN`

### SINH

``````SINH(X)
``````

Description

Computes the hyperbolic sine of X where X is specified in radians. Generates an error if overflow occurs.

If X is `NUMERIC` then, the output is `FLOAT64`.

X SINH(X)
`+inf` `+inf`
`-inf` `-inf`
`NaN` `NaN`

### ASIN

``````ASIN(X)
``````

Description

Computes the principal value of the inverse sine of X. The return value is in the range [-π/2,π/2]. Generates an error if X is outside of the range [-1, 1].

If X is `NUMERIC` then, the output is `FLOAT64`.

X ASIN(X)
`+inf` `NaN`
`-inf` `NaN`
`NaN` `NaN`
X < -1 Error
X > 1 Error

### ASINH

``````ASINH(X)
``````

Description

Computes the inverse hyperbolic sine of X. Does not fail.

If X is `NUMERIC` then, the output is `FLOAT64`.

X ASINH(X)
`+inf` `+inf`
`-inf` `-inf`
`NaN` `NaN`

### TAN

``````TAN(X)
``````

Description

Computes the tangent of X where X is specified in radians. Generates an error if overflow occurs.

X TAN(X)
`+inf` `NaN`
`-inf` `NaN`
`NaN` `NaN`

### TANH

``````TANH(X)
``````

Description

Computes the hyperbolic tangent of X where X is specified in radians. Does not fail.

If X is `NUMERIC` then, the output is `FLOAT64`.

X TANH(X)
`+inf` 1.0
`-inf` -1.0
`NaN` `NaN`

### ATAN

``````ATAN(X)
``````

Description

Computes the principal value of the inverse tangent of X. The return value is in the range [-π/2,π/2]. Does not fail.

If X is `NUMERIC` then, the output is `FLOAT64`.

X ATAN(X)
`+inf` π/2
`-inf` -π/2
`NaN` `NaN`

### ATANH

``````ATANH(X)
``````

Description

Computes the inverse hyperbolic tangent of X. Generates an error if X is outside of the range [-1, 1].

If X is `NUMERIC` then, the output is `FLOAT64`.

X ATANH(X)
`+inf` `NaN`
`-inf` `NaN`
`NaN` `NaN`
X < -1 Error
X > 1 Error

### ATAN2

``````ATAN2(X, Y)
``````

Description

Calculates the principal value of the inverse tangent of X/Y using the signs of the two arguments to determine the quadrant. The return value is in the range [-π,π].

If Y is `NUMERIC` then, the output is `FLOAT64`.

X Y ATAN2(X, Y)
`NaN` Any value `NaN`
Any value `NaN` `NaN`
0.0 0.0 0.0
Positive Finite value `-inf` π
Negative Finite value `-inf`
Finite value `+inf` 0.0
`+inf` Finite value π/2
`-inf` Finite value -π/2
`+inf` `-inf` ¾π
`-inf` `-inf` -¾π
`+inf` `+inf` π/4
`-inf` `+inf` -π/4
[{ "type": "thumb-down", "id": "hardToUnderstand", "label":"Hard to understand" },{ "type": "thumb-down", "id": "incorrectInformationOrSampleCode", "label":"Incorrect information or sample code" },{ "type": "thumb-down", "id": "missingTheInformationSamplesINeed", "label":"Missing the information/samples I need" },{ "type": "thumb-down", "id": "otherDown", "label":"Other" }]
[{ "type": "thumb-up", "id": "easyToUnderstand", "label":"Easy to understand" },{ "type": "thumb-up", "id": "solvedMyProblem", "label":"Solved my problem" },{ "type": "thumb-up", "id": "otherUp", "label":"Other" }]