Section B.1. Formatting Numbers
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. |
|
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 rightmost three digits |
|
23456 |
9,999.99 |
######### |
Overflow condition |
|
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.
