Oracle Number Functions

Number Functions

Number functions accept numeric input and return numeric values. Most of these functions return values that are accurate to 38 decimal digits. The transcendental functions COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH are accurate to 36 decimal digits. The transcendental functions ACOS, ASIN, ATAN, and ATAN2 are accurate to 30 decimal digits. The number functions are:

ABS

ACOS

ASIN

ATAN

ATAN2

BITAND

CEIL

COS

COSH

EXP

FLOOR

LN

LOG

MOD

POWER

ROUND (number)

SIGN

SIN

SINH

SQRT

TAN

TANH

TRUNC (number)

WIDTH_BUCKET

 

ABS

Syntax

abs::=

ABS(n)

Purpose

ABS returns the absolute value of n.

Examples

The following example returns the absolute value of -15:

SELECT ABS(-15) "Absolute" FROM DUAL;

  Absolute

----------

        15

 

CEIL

Syntax

ceil::=

CEIL(n)

Purpose

CEIL returns smallest integer greater than or equal to n.

Examples

The following example returns the smallest integer greater than or equal to 15.7:

SELECT CEIL(15.7) "Ceiling" FROM DUAL;

   Ceiling

----------

        16

 

FLOOR

Syntax

floor::=

FLOOR (n)

 

Purpose

FLOOR returns largest integer equal to or less than n.

Examples

The following example returns the largest integer equal to or less than 15.7:

SELECT FLOOR(15.7) "Floor" FROM DUAL;

     Floor

----------

        15

 

MOD

Syntax

mod::=

MOD ( m , n )

Purpose

MOD returns the remainder of m divided by n. Returns m if n is 0.

Examples

The following example returns the remainder of 11 divided by 4:

SELECT MOD(11,4) "Modulus" FROM DUAL;

   Modulus

----------

         3

This function behaves differently from the classical mathematical modulus function when m is negative. The classical modulus can be expressed using the MOD function with this formula:

m - n * FLOOR(m/n)

The following table illustrates the difference between the MOD function and the classical modulus:

m	n	MOD(m,n)	Classical Modulus
11	4	3	3
11	-4	3	-1
-11	4	-3	1
-11	-4	-3	-3

 

POWER

Syntax

power::=

POWER ( m , n )

Purpose

POWER returns m raised to the nth power. The base m and the exponent n can be any numbers, but if m is negative, then n must be an integer.

Examples

The following example returns 3 squared:

SELECT POWER(3,2) "Raised" FROM DUAL;

    Raised

----------

         9

 

ROUND (number)

Syntax

round_number::=

ROUND ( number [, integer ] )

Purpose

ROUND returns number rounded to integer places right of the decimal point. If integer is omitted, then number is rounded to 0 places. integer can be negative to round off digits left of the decimal point. integer must be an integer.

Examples

The following example rounds a number to one decimal point:

SELECT ROUND(15.193,1) "Round" FROM DUAL;

     Round

----------

      15.2

The following example rounds a number one digit to the left of the decimal point:

SELECT ROUND(15.193,-1) "Round" FROM DUAL;

     Round

----------

        20

 

SQRT

Syntax

sqrt::=

SQRT ( n )

Purpose

SQRT returns the square root of n. The value n cannot be negative. SQRT returns a real number.

Examples

The following example returns the square root of 26:

SELECT SQRT(26) "Square root" FROM DUAL;

Square root

-----------

5.09901951 

 

TRUNC (number)

Syntax

trunc_number::=

TRUNC ( n [, m] )

Purpose

The TRUNC (number) function returns n truncated to m decimal places. If m is omitted, then n is truncated to 0 places. m can be negative to truncate (make zero) m digits left of the decimal point.

Examples

The following example truncate numbers:

SELECT TRUNC(15.79,1) "Truncate" FROM DUAL;

  Truncate

----------

      15.7

SELECT TRUNC(15.79,-1) "Truncate" FROM DUAL;

  Truncate

----------

        10