数値文字列、浮動小数点型を NUMBER 型に変換

syntax

  • TO_NUMBER ( expr [, format [, nls_param]] )
  • TO_NUMBER ( expr [ DEFAULT def_val ON CONVERSION ERROR ] ↵
               [, format [, nls_param]] ) Oracle 12cr2

  return [ number ]

parameter

return

  • フォーマット書式に従って変換された NUMBER 型
    Oracle 12c R2 以降の場合、format 書式にて変換出来ない値を def_val で戻すことが可能。

avail

  • SQL および PL/SQL の両方で使用可能。

変換エラー時のデフォルト値

フォーマットエラーとならないバインド変数または文字・数値 リテラル
カラム名は使用できない(Oracle 12c R2 時点)

フォーマット文字列

(注意) DEFAULT ~ ON CONVERSION ERROR を使用した場合、リテラル に制限される。
  変数やカラムを使用したい場合 VALIDATE_CONVERSION と CASE 式で記述する。

各国語サポートパラメータ

nls_param の種類設定例
NLS_NUMERIC_CHARACTERSNLS 関連パラメータ 参照
NLS_CURRENCY
NLS_ISO_CURRENCY
NLS_DUAL_CURRENCY

TO_NUMBER 関数の内容

数値文字列式、または、浮動小数点(BINARY_FLOAT、BINARY_DOUBLE)の expr を フォーマット文字列 format 書式にしたがって NUMBER 型に変換する。
書式の指定方法により、符号の位置や有無、通貨文字を含んだ文字列などを数値に変換する。
(数値の詳しい書式へ...)

TO_NUMBER 使用例

SQL> select str, fmt, TO_NUMBER(str, fmt) from to_number_sample;
 
STR                  FMT                  TO_NUMBER(STR,FMT)
-------------------- -------------------- ------------------
 1                   9                                     1
-1                   9                                    -1
    -1               9                                    -1
+1                   S9                                    1
1,234.5              9,999.9                          1234.5
12345.6              00000D0                         12345.6

エラーになる使用例 ORA-01722: 数値が無効です。

TO_NUMBER(' 1', 'S9')         ⇒ 符号指定があるのに空白
TO_NUMBER('1,234.5', '9,999') ⇒ フォーマット定義より精度が小さい
TO_NUMBER('1,234.5', '999.9') ⇒ オーバーフローする

以上のように TO_CHAR 関数 と異なり桁あふれの場合はもちろん丸め処理が発生する数値文字列の場合でもエラーになる。

各国語サポートパラメータを使用した例

SQL> select TO_NUMBER('-123円','999L','NLS_CURRENCY = ''円''') yen from dual;
 
       YEN
----------
      -123

DEFAULT ... ON CONVERSION ERROR 句の使用例 Oracle 12cr2

SQL> select
  2    TO_NUMBER('12.98' DEFAULT -1     ON CONVERSION ERROR, '999,999.99') VALID_NUM,
  3    TO_NUMBER('    '  DEFAULT -1     ON CONVERSION ERROR, '999,999.99') INVALID_NUM,
  4    TO_NUMBER('0010'  DEFAULT 'FFFF' ON CONVERSION ERROR, 'XXXX') VALID_HEX,
  5    TO_NUMBER('X010'  DEFAULT 'FFFF' ON CONVERSION ERROR, 'XXXX') INVALID_HEX
  6  from dual;
 
 VALID_NUM INVALID_NUM	VALID_HEX INVALID_HEX
---------- ----------- ---------- -----------
     12.98	    -1	       16	65535
SQL> variable def_num NUMBER=-1
SQL> variable def_hex varchar2(30)='FFFF' /* 定義時代入できるのは Oracle 12cR2 以降*/
 
SQL> select
  2    to_number('$12.98' DEFAULT :def_num ON CONVERSION ERROR, '999,999.99') INVALID_NUM,
  3    to_number('000010' DEFAULT :def_hex ON CONVERSION ERROR, 'XXXX') INVALID_HEX
  4  from dual;
 
INVALID_NUM INVALID_HEX
----------- -----------
	 -1	  65535
 


数値型の優先順位

数値型の優先順位
BINARY_DOUBLE と NUMBER を計算すると NUMBER 型の値を優先順位の高い BINARY_DOUBLE 型に変換したのちに計算される。

数値の優先順位数値のデータ型
1BINARY_DOUBLE
2BINARY_FLOAT
3NUMBER

SQL 関数 - TO_NUMBER 関連

 
アルファベット別 関数一覧 ショートカット
ABCDEFG
HILMNOP
RSTUVWX
日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ Oracle Web セミナー