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. ...

Get Oracle SQL*Plus: The Definitive Guide, 2nd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.