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>
``````