Oracle SQL - Function TRUNC function

Introduction

TRUNC - Returns the truncated value (removes decimal part of a number, precision adjustable).

TRUNC simply removes decimal values.

Demo

SQL>
SQL>-- from ww  w. j  av a  2 s  .c  o  m
SQL> drop table my_table;

Table dropped.

SQL> CREATE TABLE my_table (
  2      LINENO NUMBER(2) not null,
  3      VALUE NUMBER(6,2) not null
  4  );
SQL>
SQL> insert into my_table values(1,1.2);
SQL> insert into my_table values(2,123.34);
SQL> insert into my_table values(3,-12.2);
SQL> insert into my_table values(4,100);
SQL> insert into my_table values(5,48);
SQL> insert into my_table values(6,-90);
SQL> insert into my_table values(7,0.19);
SQL>
SQL> SELECT lineno, value, ROUND(value), TRUNC(value), CEIL(value),
  2  FLOOR(value)
  3  FROM my_table;

   LINENO |     VALUE | ROUND(VALUE) | TRUNC(VALUE) | CEIL(VALUE) | FLOOR(VALUE)
--------- | --------- | ------------ | ------------ | ----------- | ------------
 00001.00 |  00001.20 |     00001.00 |     00001.00 |    00002.00 |     00001.00
 00002.00 |  00123.34 |     00123.00 |     00123.00 |    00124.00 |     00123.00
 00003.00 | -00012.20 |    -00012.00 |    -00012.00 |   -00012.00 |    -00013.00
 00004.00 |  00100.00 |     00100.00 |     00100.00 |    00100.00 |     00100.00
 00005.00 |  00048.00 |     00048.00 |     00048.00 |    00048.00 |     00048.00
 00006.00 | -00090.00 |    -00090.00 |    -00090.00 |   -00090.00 |    -00090.00
 00007.00 |  00000.19 |     00000.00 |     00000.00 |    00001.00 |     00000.00

7 rows selected.

SQL>

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

Demo

SQL>
SQL> SELECT lineno, value, ROUND(value,1), TRUNC(value,1)
  2  FROM my_table
  3--  w w  w. j  a va 2s. co m

The second argument defaults to 0.

The following query may be compared with previous versions, which have no second argument:

Demo

SQL>
SQL> SELECT lineno, value, ROUND(value,0), TRUNC(value,0)
  2  FROM my_table;

   LINENO |     VALUE | ROUND(VALUE,0) | TRUNC(VALUE,0)
--------- | --------- | -------------- | --------------
 00001.00 |  00001.20 |       00001.00 |       00001.00
 00002.00 |  00123.34 |       00123.00 |       00123.00
 00003.00 | -00012.20 |      -00012.00 |      -00012.00
 00004.00 |  00100.00 |       00100.00 |       00100.00
 00005.00 |  00048.00 |       00048.00 |       00048.00
 00006.00 | -00090.00 |      -00090.00 |      -00090.00
 00007.00 |  00000.19 |       00000.00 |       00000.00

7 rows selected.--  www .  j  ava2  s. c  om

SQL>

The second argument, precision, may be negative, which means displacement to the left of the decimal point:

Demo

SQL>
SQL> SELECT lineno, value, ROUND(value,-1), TRUNC(value,-1)
  2  FROM my_table;

   LINENO |     VALUE | ROUND(VALUE,-1) | TRUNC(VALUE,-1)
--------- | --------- | --------------- | ---------------
 00001.00 |  00001.20 |        00000.00 |        00000.00
 00002.00 |  00123.34 |        00120.00 |        00120.00
 00003.00 | -00012.20 |       -   1     |       -00010.00
 00004.00 |  00100.00 |        00100.00 |        00100.00
 00005.00 |  00048.00 |        00050.00 |        00040.00
 00006.00 | -00090.00 |       -00090.00 |       -00090.00
 00007.00 |  00000.19 |        00000.00 |        00000.00

7 rows selected.-- from w ww  . j  a  v  a2 s.c  o m

SQL>

In this example, with -1 for the precision argument, values less than 5 will be truncated to 0, and values of 5 or greater will be rounded up to 10.

• Previous
• Next