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.
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 information about number format models, see 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.
The values of some formats are determined by the value of NLS_TERRITORY.
All number format models cause the number to be rounded to the specified number of significant digits. Table 8-10, "Possible Effects of Rounding" identifies some of the effects of rounding.
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: