Formatting and Rounding Numbers with TO_CHAR, ROUND, and TRUNC

Oracle AI Database 26ai provides several ways to transform a numeric value for calculation or presentation. This lesson focuses on three established SQL functions: TO_CHAR converts a number to formatted text, ROUND returns a number rounded at a requested position, and TRUNC returns a number with digits beyond that position removed.

These functions return values from expressions; a SELECT statement that uses them does not change the stored column. Keep that distinction in mind when deciding whether the requirement concerns a calculation, displayed text, or permanently stored data.

The numeric form of TO_CHAR has this general syntax:

TO_CHAR(number [, format_model [, nls_parameter]])

The input can be a NUMBER, BINARY_FLOAT, or BINARY_DOUBLE. If the format model is omitted, Oracle returns a VARCHAR2 value long enough to contain the significant digits. That conversion is valid, but it does not guarantee a fixed number of decimal places, group separators, or a particular decimal character.

Reports, form letters, exported files, and user interfaces usually need deliberate formatting. The following query converts each sale amount to text with two fractional digits and concatenates it into a sentence:

SELECT 'You spent ' ||
       TO_CHAR(
         total_sale_amount,
         'FM999G999G990D00',
         'NLS_NUMERIC_CHARACTERS = ''.,'''
       ) ||
       ' last month.' AS valued_customer
FROM   customer_sale;

The format model contains several elements:

• FM enables fill mode, suppressing padding introduced by the number format.
• 9 permits a digit without forcing insignificant leading zeros.
• 0 requires a displayed digit, including two digits after the decimal character.
• G represents the group separator.
• D represents the decimal character.
• The third argument selects a period as the decimal character and a comma as the group separator for this expression.

Using the lesson's sample data, the query produces:

VALUED_CUSTOMER
------------------------------
You spent 108.03 last month.
You spent 40.45 last month.
You spent 52.66 last month.
You spent 61.90 last month.
You spent 20.47 last month.

The explicit format produces 61.90 rather than 61.9. Trailing zeros are presentation, not additional numeric precision stored in a NUMBER. A numeric client can display the same value differently until it is converted to formatted text.

Do not add a currency symbol unless the data model identifies the currency. Two fractional digits alone do not establish whether a value is in dollars, euros, or another unit. When the application should follow the user's locale, choose suitable NLS settings instead of hard-coding the separators used in this deterministic example.

A format model also has a finite width. If a value has more significant digits than the model can represent, Oracle can return pound signs instead of the expected digits. Select a format that accommodates the validated range of the data.

Because TO_CHAR returns character data, apply it when text is actually needed. Continue to use the original numeric expression for arithmetic, range predicates, aggregation, and numeric ordering. Character ordering compares text rather than numeric magnitude, so formatted values such as 100.00 and 20.00 may not sort in the order a numeric report expects.

SELECT total_sale_amount,
       TO_CHAR(
         total_sale_amount,
         'FM999G999G990D00',
         'NLS_NUMERIC_CHARACTERS = ''.,'''
       ) AS display_amount
FROM   customer_sale
ORDER  BY total_sale_amount;

This query sorts with the numeric column while returning a separate formatted value for display. The same principle applies to filtering: compare a numeric column with a numeric bind value instead of converting the column to text and comparing formatted strings. Keeping calculation and presentation separate also avoids repeated conversions and makes the intent of the SQL easier to review.

Format at the database layer when the SQL result itself is a report, label, or fixed-format export. Applications that already have reliable locale-aware formatting may instead retrieve the number and format it at their presentation boundary. Whichever layer performs the conversion should own one clearly defined format policy.

ROUND and numeric TRUNC use similar arguments but perform different operations. ROUND examines the discarded digits and may adjust the last retained digit. TRUNC removes the discarded digits without making that adjustment.

Precision argument	Position affected	Example interpretation
Positive	Right of the decimal point	2 operates at hundredths.
Zero	Units	0 produces a whole-number result.
Omitted	Units	For these examples, omission has the same value effect as 0.
Negative	Left of the decimal point	-1 operates at tens and -2 at hundreds.

Suppose shipping and handling is calculated as 10 percent of a sale, making the adjusted amount 110 percent of the original. A common table expression can calculate that amount once per row and apply both functions at three precision positions:

WITH shipping_amounts AS (
  SELECT total_sale_amount * 1.1 AS with_shipping
  FROM   customer_sale
)
SELECT with_shipping,
       ROUND(with_shipping, 2)  AS round_cents,
       TRUNC(with_shipping, 2)  AS trunc_cents,
       ROUND(with_shipping, 0)  AS round_dollars,
       TRUNC(with_shipping, 0)  AS trunc_dollars,
       ROUND(with_shipping, -2) AS round_hundreds,
       TRUNC(with_shipping, -2) AS trunc_hundreds
FROM   shipping_amounts;

The sample values produce the following comparisons:

WITH_SHIPPING	ROUND_CENTS	TRUNC_CENTS	ROUND_DOLLARS	TRUNC_DOLLARS	ROUND_HUNDREDS	TRUNC_HUNDREDS
118.833	118.83	118.83	119	118	100	100
44.495	44.5	44.49	44	44	0	0
57.926	57.93	57.92	58	57	100	0
68.09	68.09	68.09	68	68	100	0
22.517	22.52	22.51	23	22	0	0

Without an ORDER BY clause, SQL does not guarantee the order of these rows. The table preserves the legacy sample sequence only to make the values easy to compare.

Precision 2: hundredths

With a precision of 2, both functions operate at the hundredths position. For 57.926, ROUND examines the third fractional digit and returns 57.93. TRUNC discards that digit and returns 57.92. For 118.833, both functions return 118.83 because the discarded digit does not cause the rounded hundredths digit to increase.

SQL*Plus may display ROUND(44.495, 2) as 44.5. Numerically, that is the same value as 44.50. Apply TO_CHAR when a report must display a fixed number of fractional digits.

Precision 0: whole numbers

A precision of 0 operates at the units position. ROUND(118.833, 0) returns 119, while TRUNC(118.833, 0) returns 118. Similarly, 57.926 becomes 58 with ROUND and 57 with TRUNC.

Precision -2: hundreds

Negative precision operates to the left of the decimal point. A precision of -2 means the hundreds position. Consequently, ROUND(57.926, -2) returns 100, while TRUNC(57.926, -2) returns 0. The value 118.833 produces 100 with either function at this position.

Numeric TRUNC should not be described as always “rounding down.” It removes digits beyond the selected position. For a negative number, that behavior moves the result toward zero:

SELECT ROUND(-124.815, 2) AS rounded_value,
       TRUNC(-124.815, 2) AS truncated_value
FROM   dual;

The result for these NUMBER literals is:

ROUNDED_VALUE  TRUNCATED_VALUE
-------------  ---------------
      -124.82          -124.81

This also distinguishes TRUNC from FLOOR. FLOOR returns the greatest integer less than or equal to its input, whereas numeric TRUNC removes digits at a selected decimal position.

If the precision argument is omitted, these numeric functions operate at zero decimal places:

SELECT ROUND(124.815) AS rounded_integer,
       TRUNC(124.815) AS truncated_integer
FROM   dual;

The results are 125 and 124. Supply the argument when its presence makes the intended position easier for a reader to see.

None of the preceding SELECT statements changes TOTAL_SALE_AMOUNT. If a shipping, tax, or other calculated amount must be stored, the data model and business rules should define its currency, precision, scale, rounding policy, and the point in the workflow where rounding occurs. Inconsistent rounding in separate ad hoc queries can produce inconsistent financial results.

When a value is assigned to a constrained NUMBER(p,s) column, Oracle enforces the target precision and scale. Assignment can round fractional digits and can fail if the resulting value cannot fit the declared precision. Test with the actual target definition rather than inferring storage behavior from how a client displays a query result.

Oracle also documents that rounding a NUMBER can occasionally differ by one rounded digit from rounding the same apparent value stored as BINARY_FLOAT or BINARY_DOUBLE. The datatypes use different internal representations. Use datatypes and rounding policies that match the application's accuracy requirements.

ROUND and TRUNC also accept datetime values, but the second argument then represents a datetime format model such as 'MONTH' or 'YEAR', not a numeric precision. Likewise, TO_CHAR has a datetime form with datetime-specific format elements. Keep the numeric and datetime forms distinct when reading documentation or writing SQL.

• TO_CHAR(number) returns VARCHAR2; an explicit format model provides controlled numeric presentation.
• ROUND can adjust the last retained digit, while numeric TRUNC simply removes digits beyond the requested position.
• Positive precision operates right of the decimal point, zero or omitted precision operates at units, and negative precision operates left.
• Numeric values do not preserve display-only details such as trailing zeros; use TO_CHAR when those details matter.
• Truncating a negative number moves it toward zero at the selected position, so TRUNC is not the same operation as FLOOR.
• A query transformation does not update stored data. Storage precision and rounding must be designed and tested separately.