|Oracle® OLAP DML Reference
11g Release 2 (11.2)
Part Number E17122-05
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
PR format elements.
TO_NUMBER(text-exp, [fmt,] [nlsparams])
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. For more information, see "Number Format Models" in Oracle Database SQL Language Reference.
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:
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 (
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.
Default Number Format Values
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 by identifying 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
D format, since they are different from the current setting for the session.
DEFINE money VARIABLE DECIMAL money = TO_NUMBER('$94 567,00', 'L999G999D00', NLS_NUMERIC_CHARACTERS ', ') SHOW money
The output of this statement is: