SQL*Plus Format Elements

The COLUMN, ACCEPT, SET NUMBER, TTITLE, BTITLE, REPHEADER, and REPFOOTER 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.

Formatting Numbers

Table 1-2 shows the format elements that may be used when formatting numeric output.

Table 1-2. Numeric format elements

Format element

Function

$

Includes a leading dollar sign in the output.

,

Places a comma in the output.

.

Marks the location of the decimal point.

0

Marks the spot at which you want to begin displaying leading zeros.

9

Represents a digit in the output.

B

Forces zero values to be displayed as blanks.

C

Marks the place where you want the ISO currency indicator to appear. For U.S. dollars, this is USD.

D

Marks the location of the decimal point.

DATE

Causes SQL*Plus to assume that the number represents a Julian date and to display it in MM/DD/YY format.

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.

FM

Prefix: removes any leading or trailing blanks from the return value.

G

Places a group separator (usually a comma) in the output.

L

Marks the place where you want the local currency indicator to appear. For U.S. dollars, this is the dollar sign character.

MI

Adds a trailing negative sign to a number and may be used only at the end of a format string. ...

Get Oracle SQL*Plus Pocket Reference, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.