文字列型カラムを範囲検索する場合の注意
テーブル内の文字列型カラム、たとえばVARCHAR2に範囲選択する場合には注意が必要です。前方一致検索に近い動きになるからです。
ある程度DBに慣れてる人なら問題ないのですが、普段あまりDBMSを使ってない人には分かりづらい部分かもしれません。
ということで実例を上げます。まずはデータを登録します。主キーのID列と区分コードを格納するvarStr列のシンプルなテーブルを作成し、区分コードとして40、41、409、40010、41001の5つを登録します。
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
に接続されました。SQL> create table test ( id number(8) primary key, varStr varchar2(20));
表が作成されました。SQL> insert into test values ( 1, '40010');
1行が作成されました。SQL> insert into test values ( 2, '40');
1行が作成されました。SQL> insert into test values ( 3, '409');
1行が作成されました。SQL> insert into test values ( 4, '41');
1行が作成されました。SQL> insert into test values ( 5, '41001');
1行が作成されました。
このテーブルに対して、varStrが40である列を検索すると結果は1行。
完全一致検索なので当然ですね。
SQL> SELECT * FROM test WHERE varStr = '40';
ID VARSTR
---------- --------------------
2 40
でも、範囲指定で40〜49を検索すると、桁数が2桁以上のカラムもヒットしちゃいます。
SQL> SELECT * FROM test WHERE varStr >= '40' AND varStr <= '49' ;
ID VARSTR
---------- --------------------
1 40010
2 40
3 409
4 41
5 41001
もちろん、型変換関数のTO_NUMBERを使って数値型同士の比較にすれば、問題なく2件のみヒットします。
SQL> SELECT * FROM test WHERE TO_NUMBER(varStr) >= 40 AND TO_NUMBER(varStr) <= 49 ;
ID VARSTR
---------- --------------------
2 40
4 41
別解としてはWHERE条件中にIN句でIN('40','41','42'・・・)と並べていく手法もありますが、条件一致させたい範囲が広くなってくると面倒ですね(笑)。
ちなみにこの動きはOracleに限った話ではなく、PostgreSQLも同様です。
postgres=# SELECT * FROM test WHERE "varStr" = '40';
id | varStr
- -
2 | 40
(1 行)
postgres=# SELECT * FROM test WHERE "varStr" >= '40' AND "varStr" <= '49' ;
id | varStr
- -
1 | 40010
2 | 40
3 | 409
4 | 41
5 | 41001
(5 行)
^
postgres=# SELECT * FROM test WHERE TO_NUMBER("varStr", '99999999') >= 40 AND TO_NUMBER("varStr", '99999999') <= 49 ;
id | varStr
- -
2 | 40
4 | 41
(2 行)
