e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第86回 コラム「数値に変換できない文字列の検出について」

2014.02.13

こんにちは。インストラクターの蓑島です。

今回はコラムです。「数値に変換できない文字列の検出について」というタイトルでお届けしたいと思います。
私の最近の体験談です。

よく、数値データを格納する表を作成する場合、本来であればその列のデータ型をNUMBER型にすべきところ、VARCHAR2などの文字型にしているというケースがあると思います。
そんなとき問題なのは、数値として不適切な文字列が格納されてしまう可能性があるということです。
例えば、'12abc3’といった文字列は数字としては不適切ですね。そんな文字列を数値として演算したりすると、数値に変換できず、プログラムでエラーとなってしまいます。
そこでそのようなエラーの原因となったデータを特定したいケースがあると思ってください。

対応として、3つの方法を考えましたので、それぞれ解説したいと思います。
もちろん他の方法もありえると思います。

では今回は以下のような表をもとに解説します。

1
2
3
4
5
6
7
8
SELECT * FROM TEST86;
 
COL1
----------
123
a123
123b
1c23

この表(TEST86表)には、COL1列という列がありますが、この列のデータ型はVARCHAR2(20)としてあります。
ここに4行ありますが、最初の行データ'123'以外はすべて数字以外の文字を含んでいるので数値としては不適切です。つまり算術演算などをするとエラーとなります。

ではこの表から数値として不適切なデータを調べる方法を解説します。

まず、方法1ですが、おそらくもっとも簡単な方法だと思います。すなわち、正規表現でのパターンマッチングを判定するREGEXP_LIKE関数をつかったSELECT文です。
しかしREGEXP_LIKE関数や正規表現になじみのない方も多いと思います。以下の例で確認してください。

【方法1】

1
2
3
4
5
6
7
SELECT * FROM TEST86 WHERE REGEXP_LIKE(COL1, '[^0-9]' );
 
COL1
----------
a123
123b
1c23

ここでREXEXP_LIKE関数の基本構文は次のとおりです。

REGEXP_LIKE(列名,'正規表現パターン')

この関数は指定した正規表現パターンにマッチするときに真(TRUE)を返し、そうでないときは偽(FALSE)を返します。また、ここで指定している正規表現パターンは、 '[^0-9]' ですが、意味は「0~9以外の文字を含む」というものになります。「0~9以外の文字を含む」文字列は数値にはできないというわけです。

REGEXP_LIKE関数は比較的最近(10g)導入されたSQL関数です。したがって、この関数や正規表現パターンの記述に慣れていない人もかなり多いと思います。さりとて昔からある LIKE演算子で'%'や'_'を使ったパターンマッチングでは、「0~9以外の文字を含む」というパターンの表現が困難です。
そこで、REGEXP_LIKE関数や正規表現に慣れていない方は、以下の方法2でも同じことができます。

【方法2】

1
2
3
4
5
6
7
8
SELECT * FROM TEST86
WHERE REPLACE ( REPLACE ( REPLACE (COL1, '1' , NULL ), '2' , NULL ), '3' , NULL ) IS NOT NULL ;
 
COL1
----------
a123
123b
1c23

ここでは説明の便宜上、使われている数字は1~3までだと思ってください。
この問い合わせのWHERE句を見ると、COL1列の'1'という文字をREPLACE関数でNULLに置き換え、さらにその結果から'2'という文字をNULLに置き換え、さらにその結果から'3'という文字をNULLに置き換え、そうした最終的な結果がNULLでないという意味です。
実際には0~9までこのようなネストしたREPLACEの記述が必要です。このように各数字(0~9)をすべてNULLに置き換えた結果がNULLでないのであれば、もともとの文字列に0~9以外の文字が含まれていたと判断できるわけです。
難しい正規表現パターンを知らなくても、REPLACEという基本的な関数をうまく使えば対応できますね。しかしWHERE句の条件が長くなります。

最後に方法3ですが、PL/SQLプログラムを使ってみました。
プログラミングであれば、違った観点から様々な方法で自在に対応できます。
例えば以下のPL/SQLプログラムは、NUMBER型変数に数値として不適切な文字列を代入するとエラーになることを利用した方法です。

【方法3】

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SET SERVEROUTPUT ON
DECLARE
    V_DUMMY   NUMBER;    -- NUMBER型の変数
BEGIN  
    FOR REC IN ( SELECT * FROM TEST86) LOOP
        BEGIN  -- カーソルFORループ文の中でネストしたブロックを使っている
            V_DUMMY := REC.COL1;  --数字以外の文字を含む文字列を代入するとエラー
        EXCEPTION
            WHEN  OTHERS  THEN   -- エラー処理でその値を表示
                  DBMS_OUTPUT.PUT_LINE( '数字以外を含む列値=' || REC.COL1);
        END ;
    END LOOP;
END ;
/
数字以外を含む列値=a123
数字以外を含む列値=123b
数字以外を含む列値=1c23
 
PL/SQLプロシージャが正常に完了しました。

ここでは5~12行目がカーソルFORループ文で、SELECT * FROM TEST86 の結果行を1行づつ処理します。
ポイントは7行目でV_DUMMYというNUMBER型の変数に文字型COL1列の値を代入しているのですが、数値として不適切だった場合はNUMBER型に変換できずエラーとなります。もう一つのポイントはエラーとなってもループの処理を継続できるように、6行目~11行目をネストしたブロックにして、エラーをネストブロック内の例外処理部(9~10行目)で例外処理していることです。これによりエラーとなっても例外処理をして正常となり、ループを継続して次の行を処理できるわけです。
これで数値に変換できないすべての列の値を表示できます。

このように、PL/SQLを利用すれば自分の考えた方法で自在に目的を果たせます。
SQL関数も便利ですが、PL/SQLもぜひ使ってみてください。

それでは今回はここまでにいたします。
次回もご期待ください。

先頭へ戻る