Lesson 5	Formatting Numeric Columns
Objective	Control the display of numeric columns.

Controlling Numeric Column Display in Oracle SQL*Plus

Lessons 3 and 4 covered formatting text columns: widths, wrapping, headings, and the new BOOLEAN clause. Numeric columns need a different vocabulary entirely. With a numeric column, you are not just worried about width; you need to decide where commas go, where the decimal point falls, how negative numbers should look, and what happens when a value simply will not fit. This lesson works through the full set of formatting characters available for numeric data in Oracle AI Database 26ai, corrects one error that has circulated in older material, and covers several format elements that rarely get mentioned at all.

The Basics: COLUMN, FORMAT, and SET NUMFORMAT

The COLUMN command, already familiar from the previous two lessons, is the primary tool for numeric formatting as well. Applied to a specific column, it looks like this:

COLUMN salary FORMAT 999,999.99

Here, salary is the column name and 999,999.99 is the format model: a template built from the special characters covered in detail below.

If you want a default format applied to every numeric column in your session rather than setting one column at a time, use SET NUMFORMAT:

SET NUMFORMAT 9,999.99

A specific COLUMN FORMAT setting always wins over a session-wide SET NUMFORMAT default; think of COLUMN FORMAT as the more specific, and therefore higher-priority, instruction. There is a third layer beneath both of these worth knowing about: SET NUMWIDTH, which sets the default column width used when neither COLUMN FORMAT nor SET NUMFORMAT has been applied at all. The precedence runs in exactly that order: COLUMN FORMAT first, then SET NUMFORMAT, then SET NUMWIDTH as the final fallback, whose own default value is 10 characters.

Numeric Formatting Characters

The table below shows the core characters used in a numeric format string, with corrected and expanded examples.

Character	Description	Format specification	Input value	Displayed output
9	Represents a digit; leading zeros are suppressed and shown as blanks.	999 999999	123 123	123 123
0	Same as 9, but does not suppress leading or trailing zeros.	099 099999 990999	123 123 123	123 000123 0123
. or D	Marks the decimal point. A format model can contain only one decimal character.	999.99	123.45	123.45
, or G	Marks a group separator. Multiple group separators are allowed, but none may appear to the right of the decimal point.	999,999.99	123456.78	123,456.78
$	Places a leading dollar sign.	$999,999.99	123456.78	$123,456.78
L, C, U	Currency symbols: L for the local currency symbol, C for the ISO currency symbol, U for the dual currency symbol.	L999 C999 U999	123 123 123	(locale-dependent symbol)123
MI	Displays a trailing minus sign after a negative value, a trailing space after a positive one. Can only appear in the last position of a format model.	9999MI	-123 123	123- 123
PR	Displays a negative value in angle brackets, a positive value with a leading and trailing space. Can only appear in the last position of a format model.	9999PR	-123 123	<123> 123
S	Forces a sign to display for both positive and negative values. Can only appear in the first or last position of a format model.	S9999 9999S	123 / -123 123 / -123	+123 / -123 123+ / 123-
B	Displays blanks for the integer part of a fixed-point number when that integer part is zero, regardless of any zeros in the format model.	B9999.99	0.45	.45
V	Multiplies the value by 10 raised to the number of 9s following the V.	999V99	1.23	00123
X or x	Displays the hexadecimal value of the rounded number, uppercase or lowercase.	XXXX	255	FF
RN or rn	Displays Roman numerals, uppercase or lowercase, for integers between 1 and 3999.	RN	14	XIV
EEEE	Scientific notation. The format model must contain exactly four E characters.	9.99EEEE	12345	1.23E+04
TM	Displays the smallest number of characters possible. Defaults to TM9. Cannot be preceded by any other element; can only be followed by a single 9 or E.	TM	123.4500	123.45

A correction worth stating directly, since it has circulated in older SQL*Plus material: PR does not display negative numbers in parentheses. It displays them in angle brackets. A negative value formatted with 9999PR shows as <123>, not (123). If you have seen or written material describing PR as producing parenthesized negatives, that description is simply wrong, not just outdated; parentheses are not part of Oracle's number format model vocabulary at all.

How SQL*Plus Handles Values That Do Not Fit

SQL*Plus may round your data to fit the specified format or field width. If a value genuinely cannot fit in the column at all, SQL*Plus displays pound signs (#) in place of the number, rather than truncating it silently or displaying something misleading.

Two rarer symbols handle genuine numeric overflow during rounding. If a positive value is extremely large and overflows during rounding, SQL*Plus displays the infinity sign (~) instead of the value. If a negative value is extremely small (a large negative magnitude) and overflows during rounding, SQL*Plus displays the negative infinity sign (-~). These are edge cases you are unlikely to hit in routine reporting, but worth recognizing if you ever see either symbol appear unexpectedly in output; it means a calculation somewhere produced a value your format model genuinely cannot represent, not a formatting mistake on your part.

A Complete Worked Example

The following example applies three different formats to three distinct values selected from the DUAL table:

SQL> COLUMN a FORMAT 999,999.99
SQL> COLUMN b FORMAT 099,999.99
SQL> COLUMN c FORMAT $999,999.99
SQL> SELECT 123.45 a,
  2         234.56 b,
  3         345.67 c
  4  FROM   dual;

          A            B              C
------------ ------------ --------------
      123.45   000,234.56        $345.67

Each column receives its own value, formatted according to its own rule: A shows 123.45 plainly, with no leading zeros needed. B shows 234.56 with leading zeros filled in per the 0 elements in its format. C shows 345.67 with a leading dollar sign. Note that this corrects an error that has appeared in some older versions of this example, where columns B and C were shown displaying column A's value instead of their own.

Width and the Space Reserved for a Sign

No matter how you format numbers, SQL*Plus always reserves space for a possible negative sign, unless your format model explicitly includes MI, S, or PR to control sign display yourself. Without one of those three elements, a negative value automatically gets a leading minus sign, and a positive value automatically gets a leading blank space in the same position, so that positive and negative values of the same magnitude line up in the same column width.

The actual column width calculation follows a precise rule worth knowing rather than guessing at: a NUMBER column's width equals the width of its heading, or the width of its FORMAT specification plus one space for the sign, whichever of the two is greater. If you have not explicitly applied COLUMN FORMAT or SET NUMFORMAT to a column at all, its width falls back to whatever SET NUMWIDTH is currently set to, which defaults to 10 characters if you have never changed it.

Putting this together: formatting numeric columns well in SQL*Plus is less about memorizing every character in the table above, and more about understanding the small number of characters you will actually reach for regularly. Commas, decimal points, dollar signs, and MI or PR for negative-value handling cover the overwhelming majority of real reporting needs. The rarer elements, Roman numerals, hexadecimal display, the V multiplier, exist for the occasional situation that specifically calls for them, and knowing they exist is often more valuable than memorizing their exact syntax until the day you actually need one.