TO_NUMBER

The TO_NUMBER function converts a formatted TEXT or NTEXT expression to a number. This function is typically used to convert the formatted numerical 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.

The OLAP DML TO_NUMBER function has the same functionality as the SQL TO_NUMBER function. For more information about the SQL TO_NUMBER function, see Oracle Database SQL Reference.

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.

Syntax

TO_NUMBER(text-exp, [fmt,] [nlsparams])

Arguments

text-exp

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.

fmt

A text expression that identifies a number format model. This model specifies how the conversion to NUMBER should be performed. For information about number format models, see the Oracle Database SQL Reference and the Oracle Database Globalization Support Guide.

The default number format identifies a period (.) as the decimal marker and does not recognize any other symbol.

nlsparams

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

symbol

A text expression that specifies the local currency symbol. It can be no more than 10 characters.

territory

A text expression that identifies the territory whose ISO currency symbol is used.

dg

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.

Notes

Default Number Format Values

The values of some formats are determined by the value of NLS_TERRITORY. (See NLS Options.).

Possible Effects of TO_NUMBER Rounding

All number format models cause the number to be rounded to the specified number of significant digits. Table 23-3, "Possible Effects of Rounding" identifies some of the effects of rounding.

Examples

Example 23-24 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.

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