Oracle TRUNC Function: A Simple Guide

The TRUNC function (for numbers) in Oracle SQL is a mathematical function that cuts off (truncates) a number to a specified number of decimal places.

This is different from the ROUND function, which rounds a number up or down. TRUNC simply cuts off the digits without any rounding.

What is the TRUNC Function in Oracle?

The TRUNC(n1, [n2]) function takes a number n1 and truncates it to n2 decimal places.

If you don't provide the second argument n2, the function will truncate the number to 0 decimal places (i.e., it removes everything after the decimal point). You can also use a negative number for n2 to truncate digits to the left of the decimal point.

TRUNC Function Syntax

The syntax for TRUNC (number) is:

TRUNC(n1, [n2])

Let's break that down:

• n1 (the number): The number you want to truncate.
• [n2] (decimal places): This is an optional integer.
  • If n2 is positive, it truncates to that many places after the decimal.
  • If n2 is 0 or omitted, it truncates to a whole number.
  • If n2 is negative, it truncates to that many places before (to the left of) the decimal.

Oracle TRUNC Function Examples

Here are two practical examples of how to use TRUNC.

Example 1: Truncating to One Decimal Place using TRUNC

This example truncates the number 15.79 to a single decimal place. Notice how the "9" is simply dropped, not rounded up.

Query:

SELECT 
  TRUNC(15.79, 1) AS "Truncate_1_Decimal"
FROM DUAL;

Result:

Truncate_1_Decimal
------------------
              15.7

Example 2: Truncating to the Left of the Decimal using TRUNC

This example uses a negative number (-1) to truncate the number 15.79. This "zeros out" the digits to the left of the decimal point, starting from the ones place.

Query:

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

Result:

Truncate_Negative_1
-------------------
                 10