Section B.1. Formatting Numbers

2017-11-03 09:05:07

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.

B 1 Formatting Numbers

Категории