Oracle: TRUNC(number) Function

By Atul Rai | Last Updated: August 14, 2023 Previous       Next

In this article, we’ll explore the TRUNC(number) function. The TRUNC(number) function in Oracle SQL is used to truncate or shorten a numeric value to a specified number of decimal places. It’s like cutting off the extra decimal digits of a number while keeping the desired level of precision intact.

Syntax:

The syntax for the TRUNC(number) function is:

TRUNC(number, [decimal_places])

• number: The number you want to truncate.
• decimal_places (optional): The number of decimal places to which you want to truncate the number. If not specified, the function will remove all decimal places.

Similar Post: Oracle: TRUNC(date) Function

1. Truncating to Whole Numbers

Suppose you have a sales dataset with decimal values representing quantities, but you want to analyze the data in whole numbers. Here’s how you can use TRUNC(number):

SELECT quantity, TRUNC(quantity) AS truncated_quantity
FROM sales;

Output:

quantity | truncated_quantity
---------|-------------------
12.345   | 12
67.890   | 67
45.678   | 45

2. Specifying Decimal Places

Imagine you have a list of exchange rates, and you want to truncate them to two decimal places to maintain consistency. You can achieve this using TRUNC(number):

SELECT currency, exchange_rate, TRUNC(exchange_rate, 2) AS truncated_rate
FROM exchange_rates;

Output:

currency | exchange_rate | truncated_rate
---------|---------------|----------------
USD      | 1.23456       | 1.23
EUR      | 0.87654       | 0.87
GBP      | 0.76543       | 0.76

3. Truncating at Specific Position

In some cases, you might need to truncate a number to a specific position, not just decimal places. For instance, you have a sequence of account numbers, and you want to keep only the first four digits:

SELECT account_number, TRUNC(account_number, -4) AS truncated_account
FROM accounts;

Output:

account_number | truncated_account
---------------|-------------------
123456789      | 123400000
987654321      | 987600000
567812349      | 567800000

References

1. TRUNC(number) – OracleDoc
2. Oracle: TRUNC(date) Function
3. Oracle: RANK() vs. DENSE_RANK() Function