Oracle's To_number function

TO_NUMBER(x [, format], [ nls_language ])Converts x to a NUMBER .

• X is the string, that would be, converted to a number. X is the string that will be converted to number.
• Format, optional, is the format, which is being used to convert X to a number. Format, optional, is the form used to convert x to number.
• Nls_language, optional, is the NLS language used to convert X to a number. Nls_language, optional, is the NLS language used to convert x to number.

Number Format Elements:

Element	Example	Description
, .	1,234.56	commas and decimal points comma and decimal point
$	$123.45	Leading dollar sign. Front $ sign
0	0012.34	Leading or trailing 0. Front-or rear-facing 0
9	123	Any digit. Any number
B	B123	Leading blank for integers. Integer type pre-whitespace character
C	C123	The ISO currency symbol defined in the Nls_iso_currency parameter. The international currency symbol defined in the Nls_iso_currency parameter
D	123d99	The current decimal character defined in the Nls_numeric_characters parameter. The default value is a period.
Eeee	1.2EEE	Returns a value in scientific notation. The return value is represented by scientific notation
G	9g123	Returns the group separator (e.g, a comma).
L	L123	Returns the local currency symbol.
MI	123MI	Negative value with trailing minus sign; Returns positive value with a trailing blank.
PR	123PR	The negative values in angle brackets.
Rn	I	Values in Roman numerals, uppercase.
Rn	I	Values in Roman numerals, lowercase.
S (prefix)	S1234	Negative values with a leading minus sign, positive values and a leading positive sign.
S (suffix)	1234S	Negative values with a trailing minus sign, positive values and a trailing positive sign.
Tm	Tm	The text minimum number format model returns the smallest number of characters possible.
U	U123	The Euro currency symbol or the nls_dual_currency parameter.
V	123v99	A value multiplied by 10n, where N was the number of 9s after the V.
X	Xxxx	The hexadecimal value. 16 Binary values

Sql>SelectTo_number (' 123.45 ') fromDual To_number (' 123.45 ')-------------------123.45sql>SelectTo_number (' 123.45 ',' 9999.99 ') fromDual To_number (' 123.45 ',' 9999.99 ')-----------------------------123.45sql>SelectTo_number (' a ',' the ') fromDual To_number (' a ',' the ')--------------------12SELECTTo_number (' $12,123.23 ',' $999,999.99 ') fromDUAL; Sql>sql>sql>sql>

Do calculation after the conversion

select to_number(‘123.45‘from dual;TO_NUMBER(‘123.45‘)+2---------------------               SELECT TO_NUMBER(‘-$12,345.67‘‘$99,999.99‘FROM dual;TO_NUMBER(‘-$12,345.67‘,‘$99,999.99‘)-------------------------------------                            -12345.67SQL>

Oracle's To_number function