B.1 Formatting Numbers
SQL*Plus offers the most options when
it comes to formatting numbers. Numeric format strings may contain
any of the elements shown in Table B-1. Table B-1. Numeric format elements|
Format element |
Function |
|---|
|
$ |
Causes a number to be displayed with a leading dollar sign. | |
, |
Places a comma in the output. | |
. |
Marks the location of the decimal point and makes it a period. | |
B |
Forces zero values to be displayed as blanks. | |
MI |
Used at the end of a format string to cause a trailing negative sign
to be displayed for negative values. | |
S |
May be used at the beginning or end of a format string, and causes a
sign to be displayed. The plus (+) sign is used to mark positive
numbers, and the minus (-) sign marks negative numbers. When you use
S, a sign is always displayed. | |
PR |
Causes negative values to be displayed within angle brackets. For
example, -123.99 is displayed as <123.99>. Positive values are
displayed with one leading and one trailing space in place of the
angle brackets. | |
D |
Marks the location of the decimal point. The specific character used
is derived from your NLS settings. | |
G |
Places a group separator (usually a comma) in the output. The
specific character used is derived from your NLS settings. | |
C |
Marks the place where you want the ISO currency indicator to appear.
For US dollars, this is USD. | |
L |
Marks the place where you want the local currency indicator to
appear. For US dollars, this is the dollar sign character. You
can't use L and C in the same format specification. | |
V |
Used to display scaled values. The number of digits to the right of
the V indicates how many places to the right the decimal point is
shifted before the number is displayed. | |
EEEE |
Causes SQL*Plus to use scientific notation to display a value. You
must use exactly four Es, and they must appear at the right end of
the format string. | |
RN |
Allows you to display a number using Roman numerals. This is the only
numeric format element where case makes a difference. An uppercase
"RN" yields uppercase Roman
numerals, while a lowercase "rn"
yields Roman numerals in lowercase. Numbers displayed as Roman
numerals must be integers, and must be between 1 and 3,999,
inclusive. | |
DATE |
Causes SQL*Plus to assume that the number represents a Julian date
and to display it in MM/DD/YY format. | |
FM |
This prefix removes any leading or trailing blanks from the return
value. | |
TM |
This prefix returns a number using the minimum number of characters.
TM stands for text minimum. Follow TM with one 9 if you want a
regular, decimal notation (the default). Follow TM with one E if you
want scientific notation to be used. | |
U |
Results in a Euro symbol being placed at the specified location. The
NLS_DUAL_CURRENCY parameter actually controls the character returned
by this format element. | |
X |
Returns a number in hexadecimal value. You can precede this element
with 0s to return leading zeros, or with FM to trim leading and
trailing blanks. X can't be used in combination with
any other numeric format elements. | |
0 |
A 0 is used to mark the spot in the result where you want to begin
displaying leading zeros. It replaces one of the 9s. The most common
location for a 0 is at the extreme left of the format string, but you
can place it elsewhere. | |
9 |
9s are used to control the number of significant digits to be
displayed. |
To format a numeric column or other number, simply string together
the format elements that yield the result you want. Except for the RN
element, none of the numeric format elements are case-sensitive.
Table B-2 contains a number of examples showing
you how these format elements work. Table B-2. Numeric format examples|
Value |
Format |
Result |
Comments |
|---|
|
123 |
9999 |
123 |
A basic number | |
1234.01 |
9,999.99 |
1,234.01 |
Comma and decimal point | |
23456 |
$999,999.99 |
$23,456.00 |
Dollar value | |
1 |
0999 |
0001 |
Leading zeros | |
1 |
99099 |
001 |
Leading zeros only within the riost three digits | |
23456 |
9,999.99 |
######### |
Overflow condition | |
0 |
099B | |
Display zeros as blanks | |
1 |
099B |
001 |
Leading zeros displayed, even with B, when the value is nonzero | |
-1000.01 |
9,999.99mi |
1,000.01- |
Trailing minus sign | |
1000.01 |
9,999.99mi |
1,000.01 |
Trailing space | |
-1001 |
S9,999 |
-1,001 |
Leading sign | |
-1001 |
9,999PR |
<1,001> |
Negative values in angle brackets | |
1001 |
9,999PR |
1,001 |
Spaces instead of angle brackets | |
1001 |
9.999EEEE |
-1.001E+03 |
Scientific notation | |
1995 |
RN |
MCMXCV |
Roman numerals, uppercase | |
1988 |
rn |
mcmlxxxviii |
Roman numerals, lowercase | |
1 |
date |
01/01/12 |
Julian date, day one |
The ACCEPT command is
unique in that it uses a format string to constrain the
user's input. However, in doing so, and especially
in older releases of SQL*Plus, it takes a loose interpretation of the
format elements shown in Table B-1. You can see
several examples of ACCEPT's use in Chapter 11. For the most part, though, only the 9, 0,
and period are useful with ACCEPT. SQL*Plus always allows for a sign somewhere when you display a
number. The default is for the sign to be positioned to the left of
the number, and the sign is displayed when the number is negative.
Positive numbers have a blank space in the leftmost position. Because
space is made for a sign character, number columns are typically one
space wider than your format specification seems to account for.
That's the default behavior. Things change when you
use S, MI, or PR. With S, you always get a sign. With MI, you get a
trailing sign, or a trailing blank for positive numbers. PR gives you
angle brackets or spaces in place of them. |