Number Format Models

Number formats are used with both the TO_CHAR and TO_NUMBER functions. You use number formats in calls to TO_CHAR to specify exactly how a numeric value should be translated into a VARCHAR2 string. You can specify the punctuation to use, the location of the positive or negative sign, and other useful items. Conversely, you use number formats in calls to TO_NUMBER to specify how a string representing a numeric value should be interpreted.

A number format mask can comprise one or more elements from Table 9-5. The resulting character string (or the converted numeric value) will reflect the combination of the format model elements that you use. You will find examples of different applications of the format models in the descriptions of both the TO_CHAR and TO_NUMBER functions.

Format elements with a description starting with "Prefix:" can be used only at the beginning of a format mask; when a description starts with "Suffix:", it can be used only at the end of a format mask. Most format elements are described in terms of their effect on a conversion of a number to its character string representation. Bear in mind that the majority of such elements may also be used in the converse manner, to specify the format of a character string to be converted into a number.

Table 9-5. Number format model elements
Format element	Description
	Each 9 represents a significant digit to be returned. Leading zeros in a number are displayed as blanks.
	Each zero represents a significant digit to be returned. Leading zeros in a number are displayed as zeros.
$	Prefix: puts a dollar sign in front of a number.
B	Prefix: returns a zero value as blanks, even if the 0 format element is used to show leading zeros.
MI	Suffix: places a minus sign (-) after the number if it is negative. If the number is positive, a trailing space is placed after the number.
S	Prefix: places a plus sign (+) in front of a positive number and a minus sign (-) in front of a negative number.
PR	Suffix: places angle brackets (< and >) around a negative value. Positive values will be given a leading and a trailing space.
D	Specifies the location of the decimal point in the returned value. All format elements to the left of the D will format the integer component of the value. All format elements to the right of the D will format the fractional part of the value. The character used for the decimal point is determined by the database parameter NLS_NUMERIC_CHARACTERS.
G	Specifies the location of the group separator (for example, a comma to separate thousands as in 6,754) in the returned value. The character used for the group separator is determined by the database parameter NLS_NUMERIC_CHARACTERS.
C	Specifies the location of the ISO currency symbol in the returned value. The NLS_ISO_CURRENCY parameter specifies the ISO currency symbol.
L	Specifies the location of the local currency symbol (such as $) in the return value. The NLS_CURRENCY parameter specifies the local currency symbol.
, (comma)	Specifies that a comma be returned in that location in the return value. This comma is used as a group separator (see the G format element).
. (period)	Specifies that a period be returned in that location in the return value. This period is used as a decimal point (see the D format element).
V	Multiplies the number to the left of the V in the format model by 10 raised to the nth power, where n is the number of 9s found after the V in the format model.
EEEE	Suffix: specifies that the value be returned in scientific notation.
RN or rn	Specifies that the return value be converted to upper- or lowercase Roman numerals. The range of valid numbers for conversion to Roman numerals is between 1 and 3999. The value must be an integer. RN returns uppercase Roman numerals, while rn returns lowercase Roman numerals.
FM	Prefix: removes any leading or trailing blanks from the return value.
TM	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.
U	Places a Euro symbol at the specified location. The NLS_DUAL_CURRENCY parameter 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 cannot be used in combination with any other format elements.

Notice that sometimes two elements can be used to specify the same thing, or seemingly the same thing. For example, you can use the dollar sign ($), comma (,), and period (.), or you can use the L, G, and D elements, respectively. The letter elements respect your current NLS settings, and return the proper characters for whatever language you are using. For example, some European languages use a comma rather than a period to represent the decimal point. The dollar-sign, comma, and period format elements are U.S.-centric, and always return those three characters. We recommend that you use the NLS-sensitive format model elements such as L, G, and D unless you have a specific reason to do otherwise.

Denoting Monetary Units Table 9-5 shows four format elements you can use to denote currency symbols. These elements are $, L, and C, and U, and you may be wondering about the differences among them. · The $ format element is U.S.-centric and always returns a dollar sign ($). · The L format element respects your current NLS_CURRENCY setting, which specifies your local currency indicator. If, for example, you set your NLS_TERRITORY to indicate that you're in the United Kingdom, NLS_CURRENCY will default to £, and the L format element will result in the £ being used as the currency indicator. · The C format element is similar to the L element, but it results in the ISO currency indicator, as specified by your current NLS_ISO_CURRENCY setting. For the United Kingdom you'll get GBP (for Great Britain Pounds), while for the United States you'll get USD (for U.S. Dollars), and so forth. · The U format element was added to support the Euro, and uses the currency indicator specified by NLS_DUAL_CURRENCY. For countries that support the Euro, the NLS_DUAL_CURRENCY setting will default to the Euro symbol ( ). To view your current NLS_CURRENCY and NLS_ISO_CURRENCY settings, you can query the NLS_SESSION_PARAMETERS system view.