O'Reilly logo

Oracle SQL*Plus: The Definitive Guide, 2nd Edition by Jonathan Gennick

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required