Oracleで文字列を数値に変換（変換不可な文字列は0にしたい時なども）

やり方

TO_NUMBER() で可能。

サンプル

SELECT 
-- 結果は1。
TO_NUMBER('1') AS HENKAN,
-- 結果は2。
TO_NUMBER('1') * 2 AS KAKEZAN 
FROM テーブル名

数値に変換できない文字列を渡すと例外

例えば TO_NUMBER('あ') は下記のように例外。

ORA-01722: 数値が無効です。

NULLや空文字は例外にならずNULL（空文字）

TO_NUMBER(NULL) や TO_NUMBER('') は例外にならず、NULL（空文字）が返る。

ざっくり言うと、OracleはNULLと空文字を同じものとして扱うので『NULL（空文字）』という書き方をしています。

1以上の整数でない文字列は0として扱いたい時

例えば下記のようなテーブル。

文字列カラム	数値カラム
100	2
	4
1,000円	3
あああ	4
01	5

『文字列カラム』はユーザー入力値がそのまま入っているので、数値に変換できない値が入っている可能性がある。
だが、例外を起こさず『文字列カラム×数値カラム』を取得したい。
仮に、文字列カラムが1以上の整数でない値（イレギュラーな値）なら0としてよい。

つまり、下記のような結果を取りたい時。

文字列カラム	数値カラム	文字列カラム×数値カラムの結果
100	2	200
	4	0
1,000円	3	0
あああ	4	0
01	5	0

サンプルSQL

SELECT  
CASE WHEN REGEXP_REPLACE(文字列カラム, '^[1-9][0-9]*$', '') IS NULL 
THEN TO_NUMBER(NVL(文字列カラム, 0)) * 数値カラム ELSE 0 END AS KEKKA
FROM テーブル名

1. REGEXP_REPLACE() で1以上の整数パターンに合致する文字列を除去。
2. 上記の結果がNULLなら 『1以上の整数の文字列』 or 『NULL（空文字）』　の どちらか 。
3. TO_NUMBER() へNULL（空文字）が渡される可能性があるので NVL() をかませる。

参考サイトさん

バージョン

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production