oracle的TO_NUMBER函數

標籤：

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

• x is the string that will be converted to a number.　　x是將要被轉換成number的字串。
• format, optional, is the format that will be used to convert x to a number.  format，可選項，是用來將x轉換成number的格式。
• nls_language, optional, is the nls language used to convert x to a number.   nls_language，可選項，是nls語言，用來將x轉換成number。

Number Format Elements:

Element	Example	Description
, .	1,234.56	Commas and decimal points  逗號和小數點
$	$123.45	Leading dollar sign. 前置$符號
0	0012.34	Leading or trailing 0. 前置或後置0
9	123	Any digit. 任一數字
B	B123	Leading blank for integers. integer類型前置空白符
C	C123	The ISO currency symbol defined in the NLS_ISO_CURRENCY parameter.定義在NLS_ISO_CURRENCY參數中的國際標準貨幣符號
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.用科學計數法表示傳回值
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 with a leading positive sign.
S (suffix)	1234S	negative values with a trailing minus sign, positive values with 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 is the number of 9s after the V.
X	XXXX	the hexadecimal value.十六進位值

SQL> select to_number(‘123.45‘) from dual;TO_NUMBER(‘123.45‘)-------------------             123.45SQL> select to_number(‘123.45‘, ‘9999.99‘) from dual;TO_NUMBER(‘123.45‘,‘9999.99‘)-----------------------------                       123.45SQL> select to_number(‘12‘, ‘99‘) from dual;TO_NUMBER(‘12‘,‘99‘)--------------------                  12SELECT TO_NUMBER(‘$12,123.23‘,‘$999,999.99‘) FROM DUAL;SQL>SQL>SQL>SQL>

Do calculation after the conversion

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

oracle的TO_NUMBER函數