文字列のフィールドのOrderbyについて

質問

Oracle9iです。(厳密にはVB6も絡んでいます)

説明の便宜上、若干簡略化しています。
テーブルtestがあります。

SQL> desc test;
名前 NULL? 型
--------- -------- ----------------
ID VARCHAR2(10)

です。

(1)testテーブルのデータが、
SQL> select id from test ;

ID
----------
3
5
11
551

のとき、select id from test where id between 1 and 4;を実行すると、
ID
-------
3

select id from test where id between '1' and '4';を実行すると、

ID
-------
3
11

となってしまいます。

別のケースで、
SQL> select id from test ;

ID
----------
3
5
11
551
C44433
aaa

6行が選択されました。
のデータの場合、
select id from test where id between 1 and 4;はORA-01722:数字が無効です　のエラーとなり、
select id from test where id between '1' and '4';だと

ID
-------
3
11

となってしまいます。

自分の希望としては
select id from test where id between '1' and '4'にて、
ID
-------
3
のみ表示をさせたいです。

(2)SQL> select id from test ;

ID
----------
3
5
11
551
C44433
aaa

のとき、たとえば、
select id from test where id between '12' and 'DD';
を実行し、12以上でDD以下のデータを取得したいのですが、
(理想)
ID
----------
551
C44433

実際は、
ID
----------
3
5
551
C44433
と出てしまいます。

仕様は上司が考えてるので、
テーブル構造にそもそも問題があるのならそれ相応の説明をしたいと思っています。
ですが、基本的にはSQLの条件文を変更したいのですが、
アドバイスをいただけたらと思います。

ちなみに説明の簡略化のためフィールドを少なくしていますので
select RPAD(id , 10) from test where ～　のように
select のあとに変換するのはできません。

実環境は基本的には select * from ～としたいので。

よろしくお願いいたします。

回答No.1

SUPER-NEO · ベストアンサー

客観的な意見になりますが、ご了承ください。

まず、コードに文字列型を使用する以上、
「1」と「11」を混在させる方法はどうかと思います。
例えば、1,2,3,・・・,10,11
というようにデータがあったとしますと、
これをソートした結果を見てみてください。

1,10,11,2,3・・・

という順序で結果が出てくると思います。
一般論としてデータベース内のコードは同じ桁数であるべきですし、
同じコード体型であるべきです。
今回、ご質問いただきましたコード設計で 10 桁となっているのなら、
仮に区分も何も無い、ただの数値項目であるであれば、
「0000000001」と「0000000011」とあるべきです。
そうすれば、
「Between '0000000001' AND '0000000004'」 という条件が
組めますよね？

あと、余談ですが、仮にこのプログラムが組めたとして、
パフォーマンス的に考えると、「SELECT * FROM ～」を使うことは
好ましくありません。
検索する項目を列挙する書き方へしたほうが、
パフォーマンス向上に繋がりますよ。
参考まで。

お礼

ほぼ予想通りの回答ですね。

＞一般論としてデータベース内のコードは同じ桁数であるべきですし
実は、なぜこの質問をしたかというと、
旧システムが桁数がバラバラなんですよね。

新システム移行の際、旧データの書き換えを禁止されているので、お客様にどう持っていくのか悩んでいます。

ありがとうございました。

回答No.2

SUPER-NEO

＃１です。

既に設計の話になってしまっているようですが、
ご参考までに。

旧データのコードと新データのコードの2種類を持つことはできないんですか？
検索用に新コードを用意して、これを条件で使用するというものですが。

お礼

検索用に新コードですか・・・

がんばって導入してみます。

ありがとうございました。