Oracle: TRUNC(number) Function

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.


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;


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;


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;


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


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

Similar Posts

About the Author

Atul Rai
I love sharing my experiments and ideas with everyone by writing articles on the latest technological trends. Read all published posts by Atul Rai.