The TO_NUMBER function converts a formatted TEXT or NTEXT expression to a number. This function is typically used to convert the formatted numeric output of one application (which includes currency symbols, decimal markers, thousands group markers, and so forth) so that it can be used as input to another application.
NUMBER. Negative return values contain a leading negative sign, and positive values contain a leading space, unless the format model contains the MI, S, or PR format elements.
A text expression that contains a number to be converted. The expression can have the TEXT or NTEXT data type. A conversion from NTEXT can result in an incorrect result when the NTEXT value cannot be interpreted as a number.
A text expression that identifies a number format model. This model specifies how the conversion to NUMBER should be performed.
The default number format identifies a period (.) as the decimal marker and does not recognize any other symbol.
A text expression that specifies the thousands group marker, decimal marker, and currency symbols used in text-exp. This expression contains one or more of the following parameters, separated by commas:
NLS_CURRENCY symbol
NLS_ISO_CURRENCY territory
NLS_NUMERIC_CHARACTERS dg
A text expression that specifies the local currency symbol. It can be no more than 10 characters.
A text expression that identifies the territory whose ISO currency symbol is used.
A text expression composed of two different, single-byte characters for the decimal marker (d) and thousands group marker (g).
These parameters override the default values specified by the NLS_CURRENCY, NLS_ISO_CURRENCY, and NLS_NUMERIC_CHARACTERS options. Refer to NLS Options for additional information.
The values of some formats are determined by the value of NLS_TERRITORY.
Possible Effects of TO_NUMBER Rounding
All number format models cause the number to be rounded to the specified number of significant digits. Table 8-17, "Possible Effects of Rounding" identifies some effects of rounding.
Example 8-144 Converting Text Data to Decimal Data
The following statements convert a text string to a DECIMAL data type in the current number format, which is American. The text-exp parameter is a text string in European format. The fmt parameter identifies the local currency symbol (L), the thousands group separator (G), and the decimal marker (D). The NLS_NUMERIC_CHARACTERS option identifies the characters used for the G and D formats in the text-exp parameter because they are different from the current setting for the session. In text_exp, D is a comma and G is a space.
DEFINE money VARIABLE DECIMAL
money = TO_NUMBER('$94 567,00', 'L999G999D00', NLS_NUMERIC_CHARACTERS ', ')
SHOW money
The output of this statement is:
94,567.00