数値判断（IS_NUMBER）

Oracleには、文字列が数値（数字のみで構成されている）かどうかを判断するIS_NUMBER()といった関数は無い。
それをしたかったら、自分で何とかするしかない。

• TO_NUMBER2()関数を自作する
• translate()関数を使用して判断する
• でもそもそも数値判断が必要なテーブルってダメじゃん
• CASE式により他項目で判定して数値化する [2010-08-31]

関数を自作する

PL/SQLで関数（FUNCTION）を作成（ストア）しておくのが一番確実で楽。

すなわち、TO_NUMBER()で文字列から数値への変換が成功するのが数値。
数値以外だとTO_NUMBER()は「ORA-01722: 数値が無効です。」のエラーを発生させるので、それをキャッチしてやればよい。

作成例：

create or replace function TO_NUMBER2(str varchar2) return number
is
begin
 return to_number(str);
exception when others then
 --dbms_output.put_line('TO_NUMBER2 error: ' || SQLERRM);
 return null;
end;
/

実行例：

SQL> set null *null*
SQL> select TO_NUMBER2('123') from dual;

TO_NUMBER2('123')
-----------------
              123

SQL> select TO_NUMBER2('abc') from dual;

TO_NUMBER2('ABC')
-----------------
*null*

参考： duranteさんの（OTN）Code Tips #1085

translate()を使う

関数を作るという手が使えない場合の代替策で、translate()を使って判断する方法が考えられる。

translate()は、特定の文字（群）を別の文字に変換する関数。
これを使って数字を空白文字に置き換え、全てが空白になれば数値と判断できる。
（ただしこの方法だけでは、符号や小数点やカンマ等は判断できない）

概要：

select
  decode(trim(translate(文字列,'0123456789',' ')), null,to_number(文字列))
from テーブル;

select
  case when trim(translate(文字列,'0123456789',' ')) is null then to_number(文字列) end
from テーブル;

実行例：

SQL> set null *null*
SQL> select decode(trim(translate('999','0123456789',' ')), null,to_number('999')) as TEST from dual;

      TEST
----------
       999

SQL> select decode(trim(translate('zzz','0123456789',' ')), null,to_number('zzz')) as TEST from dual;

      TEST
----------
*null*

decode()関数とcase式は、どっちの方が実行効率がいいんでしょうねぇ？？

ちなみに、この場合、NVL2()を使うのはダメ。

select
  nvl2(trim(translate(文字列,'0123456789',' ')), null, to_number(文字列))
from テーブル;

NVL2()は、第1引数がNULLかどうかに応じて第2引数か第3引数の値を返す。
これらの引数全ての評価（演算）はNVL2()が呼ばれる前に実行されるので、第1引数の判断結果に関わらず第3引数のTO_NUMBER()が実行されてしまう（TO_NUMBER()の引数が数値以外ならエラーが発生してしまう ので、目的を達成できない）。

なお、translate()の第3引数は、第2引数の文字数と同じ個数のスペースを入れておくのが本来の姿だと思う。
第2引数のn文字目にマッチした文字が、第3引数のn文字目の文字に変換される為。

	decode(trim(translate(文字列,'0123456789','          ')), null,to_number(文字列))

ここで第3引数の文字が足りない場合、文字自体が空文字に変換される（つまり文字が無くなる）らしい。
したがって、それでも今回の目的には合致している（空白に変換したところで、trim()で削っちゃう）ので問題ない。

しかし、だからと言って第3引数のスペースを一切無くして（空白を0個にして）「''」にしてしまうと、Oracleでは空文字列はNULLとして扱われてしまう為か、上手く変換されない。

IS_NUMBERが必要な状況とは？

“文字列が数値かどうかを判断する必要がある”とは、どういう状況だろうか？

例えば以下のようなテーブルがあるとする。

データIDが0001のときは商品データを示し、データ1には商品番号、データ2には商品名、データ3には定価が入るものとする。
データIDが1111のときは販売データを示し、データ1には顧客番号、データ2には販売金額が入るものとする。

すると、データ2だけを見た場合、文字列（商品名）や数値（金額）が入り混じることになる。

販売金額が1000円以上のものだけを取り出したいとすると、以下のようなSQLを書きたくなる。

select * from 汎用テーブル
where
  データID           =  '1111' and
  TO_NUMBER(データ2) >= 1000
;

しかし、SQLの実行計画では「データ1の条件を先に判断して、そこで条件外だったときに後の条件を無視する（処理しない）」とは限らない。
データIDが0001であってもTO_NUMBER()が実行されてしまい、（数値でない）文字列なのでエラーになってしまう。
なお、データ2が空文字列（NULL）の場合はTO_NUMBER()はNULLになってくれるので、WHERE条件にはひっかからないしエラーにもならない。

そこで、文字列が数値かどうか判断するIS_NUMBER()があれば、decode()やcase式を使って“数値のときだけTO_NUMBER()を実行する”という手が採れる。
（もしくはTO_NUMBER2()のような、数値変換できない文字列だったら（エラーでなく）NULLを返す変換関数でもよい）

→CASE式で判定する方法

が、そもそも、こんな汎用テーブルはRDBの設計としておかしい。
データIDが違うと保持するデータが異なる（属性も違うし、なによりデータの意味/内容が異なる）ので、データID毎に別テーブルにすべきだ。
そうすれば、金額が入るエリアは素直にNUMBER型にすることが出来、変換の必要性すら無くなる。
また、「データ1」などという内容が分からない（意味不明な）論理名でなく、「定価」とか「販売金額」とか、意味が明瞭に伝わる名前にすることが出来る。

想像だが、汎用機（メインフレーム／ホスト）のファイル（レイアウト）をそのままRDBに持ってきたらこんな風になるんじゃなかろうか。
汎用機時代は（それなりにファイルのデータ領域節約が必要なので）、こういった項目の使い回しをしていた気がする。

COBOLは分からないのでPL/Iで書いてみると、以下のような感じか。
PL/IのDEFINE（領域再定義。C言語のunion相当。メモリー上の同じ領域を、別名・別属性で読み書きする）を駆使。

DCL
   1   汎用テーブル,
    2  キー       PIC'(15)9',
    2  データID   CHAR(4),
    2  データ1    CHAR(200),
    2  データ2    CHAR(200),
    2  データ3    CHAR(200),
   1   商品データ DEF 汎用テーブル
    2  ##01       CHAR(15),   /* キー相当 */
    2  ##02       CHAR(04),   /* データID相当 */
    2  商品番号   CHAR(010),  /* データ1 */
    2  ##03       CHAR(190),
    2  商品名     CHAR(200),  /* データ2 */
    2  定価       PIC'(15)9', /* データ3 */
    2  ##04       CHAR(185);

PL/Iは変数名に漢字使えないけど、イメージで。

「データIDが関連している（つまりオブジェクト指向で言う継承関係がある）から1つのテーブルにするんだ」とか主張するなら、
RDB（リレーショナルDB）でなく、オブジェクト指向DB（だったっけ？よく知らないので想像だけど）で実現した方がいいかもしれない。

Java（JDK1.5）で書けば、こんな感じ？

class 汎用テーブル {
	private long キー;
	private String データID;
	private Object データ1, データ2, データ3;

	private static long キー最大値 = 1;

	protected 汎用テーブル(String id, Object d1, Object d2, Object d3) { //コンストラクター
		キー     = キー最大値++;
		データID = id;
		データ1  = d1; データ2 = d2; データ3 = d3;
	}

	public long   getキー()     { return キー; }
	public String getデータID() { return データID; }

	public Object getデータ1() { return データ1; }
	public Object getデータ2() { return データ2; }
	public Object getデータ3() { return データ3; }
}

class 商品データ extends 汎用テーブル {
	public 商品データ(long 商品番号, String 商品名, long 定価) { //コンストラクター
		super("0001", 商品番号, 商品名, 定価); //オートボクシング(long→Long)使用
	}

	@Override public Long   getデータ1() { return (Long)  super.getデータ1(); }
	@Override public String getデータ2() { return (String)super.getデータ2(); }
	@Override public Long   getデータ3() { return (Long)  super.getデータ3(); }

	public long   get商品番号() { return getデータ1(); }
	public String get商品名()   { return getデータ2(); }
	public long   get定価()     { return getデータ3(); }
}

↓
いや、やっぱり普通はこう設計するよね。
↓

class 汎用テーブル {
	private long キー;
	private String データID;

	private static long キー最大値 = 1;

	protected 汎用テーブル(String id) { //コンストラクター
		キー     = キー最大値++;
		データID = id;
	}

	public long getキー() { return キー; }
	public String getデータID() { return データID; }
}

class 商品データ extends 汎用テーブル {
	private long   商品番号;
	private String 商品名;
	private long   定価;

	public 商品データ(long 商品番号, String 商品名, long 定価) { //コンストラクター
		super("0001");
		this.商品番号 = 商品番号;
		this.商品名   = 商品名;
		this.定価     = 定価;
	}

	public long   get商品番号() { return 商品番号; }
	public String get商品名()   { return 商品名;   }
	public long   get定価()     { return 定価;     }
}

やはり、データ1〜3といった、多目的の共有項目を持つような設計はおかしい（時代錯誤）でしょ。

強いて「getデータ1()」というメソッドが欲しいなら、こうするかな。

class 汎用テーブル {
	〜
	public Object getデータ1() { throw new UnsupportedOperationException(); }
	public Object getデータ2() { throw new UnsupportedOperationException(); }
	public Object getデータ3() { throw new UnsupportedOperationException(); }
}

class 商品データ extends 汎用テーブル {
	〜
	@Override public Object getデータ1() { return get商品番号(); }
	@Override public Object getデータ2() { return get商品名();   }
	@Override public Object getデータ3() { return get定価();     }
}

すなわち、データを保持するクラス（データの主管）は汎用テーブルではなく、各クラス（テーブル）のはず。

CASE式により他項目で判定して変換する方法

他の項目によって必ず数字しか入っていないと識別できる場合、それを条件にして数値変換することが出来る。[2010-08-31]

select * from 汎用テーブル
where
	(case when データID = '1111' then TO_NUMBER(データ2) end) > 1000
--	(case データID   when '1111' then TO_NUMBER(データ2) end) > 1000
;

データIDが'1111'以外のレコードではCASE式の結果がnullになるので、必ず条件外となる。（→NULLとの比較について）

以下のようにTO_NUMBER()を直接使うと、データIDが'1111'（データ2が数値となる）以外のレコードの時も変換を行ってエラーとなることがある。

SQL> select * from 汎用テーブル
  2  where
  3	データID = '1111' and
  4	TO_NUMBER(データ2) > 1000
  5  ;
TO_NUMBER(データ2) > 1000
*
行4でエラーが発生しました。:
ORA-01722: 数値が無効です。

※条件判定（この例のTO_NUMBER(データ2)>1000）は、WHERE内の他の条件（この例のデータID='1111'）（先に在っても後に在っても）によって除外されず、常に実行されると考えた方がよい
（CASE式では、WHENを満たした時しかTHENが実行されない）