Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-05
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF


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.

Return Value

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.


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

Usage Notes

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 G and D format, since they are different from the current setting for the session.

money = TO_NUMBER('$94 567,00', 'L999G999D00', NLS_NUMERIC_CHARACTERS ', ')
SHOW money

The output of this statement is: