Several SQL*Plus commands allow you to control data formats using what is called a format specification. A format specification is a string of characters that tells SQL*Plus exactly how to format a number, date, or text string when it is displayed. The most notable of these commands is the COLUMN command, which is used to format columns of output from a SELECT query. There are other commands as well. The complete list of SQL*Plus commands that accept format specification strings is shown here:
- ACCEPT
- COLUMN
Controls various aspects of the way a column of data is displayed.
- SET NUMBER
Defines the default display format for numbers.
- TTITTE, BTITLE, REPHEADER, REPFOOTER
These commands all allow number format specifications to control the way numbers are formatted in page headers, page footers, report headers, and report footers.
There are three different, broad types of values SQL*Plus can format: numbers, character strings, and dates. Not all commands can handle each type. With most commands, you can only specify number and date formats. The COLUMN command is a good example. The ACCEPT command is the only one that allows you to specify a date format string.
Format specification strings are made up of special characters that have meaning to SQL*Plus in the context of formatting a value for display. Numeric format strings, for example, tend to have lots of 0s, 9s, decimal points, and dollar signs. Date format strings tend to include things like MM, DD, YYYY, and so forth. Character string formats are the simplest of all, because you basically have only one thing you can influence: length.
SQL*Plus offers the most options when it comes to formatting numbers. Numeric format strings may contain any of the elements shown in Table 2.1.
Table B-1. Numeric Format Elements
|
Format Element |
Function |
|---|---|
|
9 |
9s are used to control the number of significant digits to be displayed. |
|
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 is at the extreme left of the format string, but you can place it elsewhere. |
|
$ |
Causes a number to be displayed with a leading dollar sign. |
|
, |
Places a comma in the output. |
|
. |
Marks the location of the decimal point. |
|
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 either the beginning or end of a format string, and causes a sign to be displayed. The + sign is used to mark positive numbers, and the - sign marks negative numbers. When you use S, a sign will always be displayed. |
|
PR |
Causes negative values to be displayed within angle brackets. For example, -123.99 will be displayed as “<123.99>”. Positive values will be displayed with one leading and one trailing space in place of the angle brackets. |
|
D |
Marks the location of the decimal point. |
|
G |
Places a group separator (usually a comma) in the output. |
|
C |
Marks the place where you want the ISO currency indicator to appear. For US dollars, this will be USD. |
|
L |
Marks the place where you want the local currency indicator to appear. For US dollars, this will be the dollar sign character. You cannot 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 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. |
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 2.2 contains a number of examples showing you how these format elements really work.
The ACCEPT command is unique in that it uses a format string to constrain the user’s input. However, in doing so, it takes a rather loose interpretation of the format elements shown in Table 2.1. You can read more about this in Chapter 7. For the most part, though, only the 9, 0, and period are very 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 only displayed when the number is negative. Positive numbers will have a blank space in the leftmost position. Because space is always made for a sign character, number columns will typically be 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.
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 |
A dollar value |
|
1 |
0999 |
0001 |
Leading zeros |
|
1 |
99099 |
001 |
Leading zeros only within the rightmost three digits |
|
23456 |
9,999.99 |
######### |
An 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 |