OracleでVARCHAR2型のカラムを比較演算したい

Question

VARCHAR2型のカラムに半角数字や全角の日本語が入っています。
このカラムから半角数字のみのデータのみ抜き出し、大小の比較を行いたいのですが数値変換がうまくいきません。

最初は単純に、
select TO_NUMBER(COL_A)
from table_a
と実行したところ、「ORA-01722 数値が無効です。」とエラーになりました。

そこで以下のように半角数字のデータのみを抜き出してから変換したところ、
「ORA-01426 数値オーバーフローが発生しました 」とエラーになりました。
select TO_NUMBER(COL_A)
from (
select COL_A
from table_a A
where NOT(REGEXP_LIKE(A.COL_A, '[^0-9]')
)

そこで今度は、以下のようにFUNCTIONを作成し
例外が発生しない場合のみTO_NUMBERを行うようにしたのですが
「数値オーバーフローです。 」とエラーになりました。

CREATE OR REPLACE FUNCTION TEST_FUNC(SOURCE IN VARCHAR2) RETURN NUMBER AS
BEGIN
RETURN TO_NUMBER(SOURCE);
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;

select TEST_FUNC(COL_A) from table_a
→数値オーバーフローです。

これ以外に思い当たるアイデアがないのですが何かいい方法はないでしょうか？

Answer 1

※11g Express Editionで確認しています。

127ケタ以上の場合にORA-01426になるみたいです。
また、NUMBER型の最大有効桁数は38桁です。
数値データ型の概要(Oracleデータ型)

select to_number('123456789012345678901234567890123456789912345678901234567890123456789012345678901234567890123456789012345678901234567890123456') from dual

--- 123456789012345678901234567890123456789900000000000000000000000000000000000000000000000000000000000000000000000000000000000000


select to_number('1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567') from dual

--- ORA-01426: 数値オーバーフローが発生しました

データ量が多いと厳しいですが、パフォーマンスを度外視するなら、LPAD関数で最大桁数に合わせて先頭にゼロを埋めて、ソートするのはどうでしょうか。

SELECT * FROM (
  SELECT LPAD(COL_A, 127, '0') AS TGT --- 列の桁数が127の場合
  FROM TABLE_A
  WHERE REGEXP_LIKE(COL_A, '^[0-9]+$'))
ORDER BY TGT

あと、直接関係ありませんが、正規表現のところは

WHERE REGEXP_LIKE(A.COL_A, '^[0-9]+$') -- 半角数字が1文字以上

のほうが良いかと思います。

Comments

[lupus_dingo]

回答ありがとうございます。

確かにこのカラムの中に127桁以上のデータがあり、
127桁の桁数制限と半角数字のみの条件を加えたところうまく動きました。
助かりました。