Oracle Number Function - Oracle/PLSQL TRUNC Function number

This Oracle tutorial explains how to use the Oracle/PLSQL TRUNC function it applying to numeric values.

The Oracle/PLSQL TRUNC function returns a number truncated to a certain number of decimal places.

Syntax

The syntax for the Oracle/PLSQL TRUNC function is:

TRUNC( number, [ decimal_places ] )

number is the number to truncate.

decimal_places is the number of decimal places to truncate to. It must be an integer and default is 0.

Example

TRUNC removes the decimal values.


SQL> select TRUNC(1.1) from dual;
--  w  w  w  . jav a  2s  .  com
TRUNC(1.1)
----------
         1

SQL> select TRUNC(1.9) from dual;

TRUNC(1.9)
----------
         1

SQL> select TRUNC(-1.1) from dual;

TRUNC(-1.1)
-----------
         -1

SQL> select TRUNC(-1.9) from dual;

TRUNC(-1.9)
-----------
         -1

SQL> select TRUNC(0) from dual;

  TRUNC(0)
----------
         0

SQL>

Keep decimal place

TRUNC function may have a second argument to handle precision, which means the distance to the right of the decimal point. The second argument defaults to 0.


SQL> select TRUNC(1.99999) from dual;
-- from   w w w .j a v  a2 s.  c  o m
TRUNC(1.99999)
--------------
             1

SQL> select TRUNC(1.9,0) from dual;

TRUNC(1.9,0)
------------
           1

SQL> select TRUNC(1.9,1) from dual;

TRUNC(1.9,1)
------------
         1.9

SQL> select TRUNC(1.99,2) from dual;

TRUNC(1.99,2)
-------------
         1.99

SQL> select TRUNC(1.999,3) from dual;

TRUNC(1.999,3)
--------------
         1.999

SQL>

Keep decimal place from left

In addition, the second argument, precision, may be negative, which means displacement to the left of the decimal point.


SQL> select TRUNC(19999.99999,0) from dual;
--   ww  w  . java  2 s .  com
TRUNC(19999.99999,0)
--------------------
               19999

SQL> select TRUNC(19.99999,-1) from dual;

TRUNC(19.99999,-1)
------------------
                10

SQL> select TRUNC(199.99999,-2) from dual;

TRUNC(199.99999,-2)
-------------------
                100

SQL> select TRUNC(1999.99999,-3) from dual;

TRUNC(1999.99999,-3)
--------------------
                1000

SQL>