Purpose Converts char, a value of CHAR or VARCHAR2 datatype containing a number in the format specified by the optional format model fmt, to a value of NUMBER datatype. Example 1 UPDATE emp SET sal = sal + TO_NUMBER('100.00', '9G999D99') WHERE ename = 'BLAKE';
The 'nlsparams' string in this function has the same purpose as it does in the TO_CHAR function for number conversions. Example 2 SELECT TO_NUMBER('-AusDollars100','L9G999D99', ' NLS_NUMERIC_CHARACTERS = '',.'' NLS_CURRENCY = ''AusDollars'' ') "Amount" FROM DUAL; Amount ---------- -100
-----------------------------
123.12实际:40
:-)
我试了一下,可以带多个参数
其中最后面的那个是字符集参数
如果数字格式在规定格式以内,可以用,在规定格式以外的话,就会出错SQL> select to_number('$12345.678', '$999999.99') from dual;
select to_number('$12345.678', '$999999.99') from dual
*
ERROR 位于第1行:
ORA-01722: 无效数字
实际:60
目的:将CHAR或VARCHAR2类型的string转换为一个NUMBER类型的数值,如果指定了format,那么string应该遵循相应的数字格式。nlsparams的用来指定小数点和钱分位分隔符,以及货币符号。它与TO_CHAR()互为反函数
TO_NUMBER('100.00', '9G999D99')
WHERE last_name = 'Perkins';SELECT TO_NUMBER('-AusDollars100','L9G999D99',
' NLS_NUMERIC_CHARACTERS = '',.''
NLS_CURRENCY = ''AusDollars''
') "Amount"
FROM DUAL;我找到两个例子的,但还是看不懂那些参数的含义。
Syntax
TO_NUMBER(char [,fmt [, 'nlsparams'] ])
Purpose
Converts char, a value of CHAR or VARCHAR2 datatype containing a number in the format specified by the optional format model fmt, to a value of NUMBER datatype.
Example 1
UPDATE emp SET sal = sal +
TO_NUMBER('100.00', '9G999D99')
WHERE ename = 'BLAKE';
The 'nlsparams' string in this function has the same purpose as it does in the TO_CHAR function for number conversions.
Example 2
SELECT TO_NUMBER('-AusDollars100','L9G999D99',
' NLS_NUMERIC_CHARACTERS = '',.''
NLS_CURRENCY = ''AusDollars''
') "Amount"
FROM DUAL; Amount
----------
-100
目的:将CHAR或VARCHAR2类型的string转换为一个NUMBER类型的数值,如果指定了format,那么string应该遵循相应的数字格式。
2.范例
DECLARE
v_Num NUMBER;
BEGIN
v_Num := TO_NUMBER('$12345.67','$99999.99');
END;
我现在要把000012134,000034301这类数据转换为数值,请问该如何用to_number()函数呢?
SQL> select to_number('00001228') from dual;TO_NUMBER('00001228')
---------------------
1228实际:1262
----------------------
12134