Oracle SQL Number Functions: A Practical Guide

Formulas & functions · Updated November 22, 2026

Oracle SQL number functions are built-in functions that accept numeric input, perform a calculation, and return a numeric value, used for rounding, truncation, and mathematical operations directly in SQL queries. For example, ROUND(45.926, 2) returns 45.93, and TRUNC(45.926, 2) returns 45.92. This article explains the most commonly used Oracle number functions, how they work, and how to choose the right one for your data.

What Are Oracle Number Functions?

Oracle SQL provides a set of scalar functions that operate on numbers. They are deterministic (same input always gives same output) and can be used in SELECT lists, WHERE clauses, and ORDER BY clauses. The functions fall into categories: rounding, truncation, absolute value, sign, modulus, and power/exponent. They accept either NUMBER or BINARY_FLOAT/BINARY_DOUBLE data types, though the exact behavior may differ for floating-point types.

Core Rounding and Truncation Functions

The most frequently used functions are ROUND and TRUNC. Both take two arguments: the number and the number of decimal places. The second argument can be negative to round or truncate to the left of the decimal point.

  • ROUND(45.926, 2) → 45.93 (rounds half up)
  • TRUNC(45.926, 2) → 45.92 (truncates, no rounding)
  • ROUND(45.926, 0) → 46 (rounds to integer)
  • ROUND(45.926, -1) → 50 (rounds to nearest ten)
  • TRUNC(45.926, -1) → 40 (truncates to nearest ten)

Note: ROUND uses “round half away from zero” for positive numbers; for negative numbers, it rounds toward negative infinity (e.g., ROUND(-45.5) returns -46). TRUNC simply removes digits without rounding.

Absolute Value, Sign, and Modulus

These functions are straightforward but essential in data cleaning and conditional logic.

  • ABS(-15) → 15 (absolute value)
  • SIGN(-15) → -1, SIGN(0) → 0, SIGN(15) → 1 (returns -1, 0, or 1)
  • MOD(10, 3) → 1 (remainder of division; works with negative numbers too, sign follows dividend)

For example, to find even or odd rows, you might use MOD(employee_id, 2) in a WHERE clause.

Exponential, Logarithmic, and Power Functions

Oracle includes functions for scientific calculations. These are less common in everyday business queries but appear in financial or engineering applications.

  • POWER(2, 10) → 1024 (base raised to exponent)
  • SQRT(16) → 4 (square root)
  • EXP(1) → 2.718281828459045 (e raised to power)
  • LN(2.718281828) → 1 (natural logarithm)
  • LOG(10, 100) → 2 (logarithm of second argument to base first argument)

Remember that LN is natural log, while LOG takes a base as its first argument. For base-10 logarithm, you can use LOG(10, x) or LOG(10, x).

Ceiling and Floor

Oracle does not have CEIL and FLOOR functions in the same way as some other databases, but it does have CEIL and FLOOR! They are available since Oracle 10g. They return the smallest integer greater than or equal to (CEIL) or the largest integer less than or equal to (FLOOR) the input.

  • CEIL(4.2) → 5
  • CEIL(-4.2) → -4 (note: moves toward positive infinity)
  • FLOOR(4.8) → 4
  • FLOOR(-4.8) → -5 (moves toward negative infinity)

These are useful for pricing, inventory (e.g., always round up to next whole unit), or age calculations.

How to Choose the Right Function

The choice depends on the business rule you need to implement. Ask yourself: do you need to round to a specific decimal place, or just drop extra digits? Do you need the nearest integer, or always the next higher/lower integer? Do you need the remainder for grouping?

  • Use ROUND for financial calculations where standard rounding is required (e.g., currency amounts).
  • Use TRUNC when you need to discard excess precision, such as when storing a timestamp as a date (though date functions are separate).
  • Use CEIL for quantities that must be whole numbers and you always round up (e.g., number of boxes needed).
  • Use FLOOR for age in years (e.g., FLOOR(months_between(sysdate, birthdate)/12)).
  • Use MOD for cyclic patterns like every nth row or alternating groups.

Also consider data type: BINARY_FLOAT and BINARY_DOUBLE may produce slightly different results due to floating-point representation; for exact decimal arithmetic, stick with NUMBER.

Common Pitfalls and Performance Considerations

One common mistake is assuming ROUND always rounds .5 up. In Oracle, ROUND(2.5) returns 3, but ROUND(-2.5) returns -3 (not -2). This is “round half away from zero” for positive numbers, but for negative numbers it rounds toward negative infinity. If you need standard “round half up” for negatives, you may need to use a CASE expression.

Another pitfall: using TRUNC on a negative number with a negative decimal places argument. TRUNC(45.926, -1) gives 40, but TRUNC(-45.926, -1) gives -40 (truncates toward zero). This is consistent but often overlooked.

Performance-wise, these functions are CPU-light and typically do not cause index suppression unless used in a WHERE clause on a indexed column. If you filter on ROUND(column, 2) = value, Oracle cannot use a regular index; consider storing the rounded value in a separate column or using a function-based index.

Frequently Asked Questions

What is the difference between ROUND and TRUNC?

ROUND rounds a number to the specified number of decimal places, using standard rounding rules (half away from zero for positives). TRUNC simply cuts off digits without rounding. For example, ROUND(3.14159, 2) is 3.14, while TRUNC(3.14159, 2) is also 3.14, but ROUND(3.145, 2) is 3.15 while TRUNC(3.145, 2) is 3.14.

How do I round to the nearest whole number in Oracle?

Use ROUND(number) with no second argument, or ROUND(number, 0). For example, ROUND(4.6) returns 5. If you always want to round up, use CEIL(4.2) which gives 5. If you always want to round down, use FLOOR(4.8) which gives 4.

Can I use number functions on columns of type BINARY_FLOAT?

Yes, most number functions accept BINARY_FLOAT and BINARY_DOUBLE arguments. However, due to floating-point representation, results may have small precision differences compared to NUMBER. For financial calculations, it is safer to use NUMBER data type.

How do I get the remainder of a division in Oracle SQL?

Use the MOD function. For example, MOD(10, 3) returns 1. This is useful for checking even/odd numbers (MOD(employee_id, 2)) or for grouping rows into buckets.

Is there a function to round up to the nearest multiple of 10?

Yes, you can combine CEIL with division and multiplication. For example, to round up to the nearest 10: CEIL(number/10)*10. For number=43, this gives 50. To round down to the nearest 10, use FLOOR(number/10)*10.

Why does ROUND(-2.5) return -3 in Oracle?

Oracle’s ROUND uses “round half away from zero” for positive numbers, but for negative numbers it rounds toward negative infinity. So ROUND(-2.5) returns -3. If you need “round half up” (i.e., -2.5 rounds to -2), you can use a custom expression: CEIL(value - 0.5) for positive and negative numbers, but test carefully.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.