【SELECT文】左の３桁内が数値か否か判断する方法を教えてください。

初めて投稿させて頂きます。よろしくお願いします。
表題の件ですが
あるAテーブルの項目の属性がCHAR型で左から３文字内で数値のみの場合と英字が含まれている場合とで判断をしたいのですが。

成功イメージ例
Aテーブル
---------------------
TEST(フィールド)
1234567
12A4567
BN34567

左から３桁内数値だけ選択
結果>>>>'1234567'のレコード１件を抽出
------>SELECTで左３桁内数値のものだけ抽出したいのですが。
SUBSTR(TEST,1,3)で左の３文字を切り離したのですが、行き詰ってしまいました。
わかりづらい説明で申し訳ございませんが、よろしくお願いいたします。

No.3 ベストアンサー 回答者： k_o_r_o_c_h_a_n 回答日時： 2006/03/20 00:05

＞＃１どの

別名に対する条件(where)記述は、文法エラーになるはずです。
また、文法エラーを回避しても、"1o9"のような値が範囲にマッチしますので
比較の考え方も再考の余地ありです。


本題ですが．．

３桁限定であるなら、
select * from 表
where
substr(TEST,1,1) between '0' and '9' and
substr(TEST,2,1) between '0' and '9' and
substr(TEST,3,1) between '0' and '9';
と書くのが判りやすく手っ取り早いと思いますよ。

桁数が長く、とても書ききれないなどの事情があるなら、
select * from 表
where replace(translate(substr(TEST,1,3),'123456789','000000000'),'0','') is null
のように、translate関数やreplace関数を使って、小細工するのが手軽な方法です。
（未検証のSQLですが）

念のため、解説すると．．
TESTフィールドの先頭３桁の数字を抹消してみて、文字列がなくなれば、３桁とも数字だった。という考え方です。

この回答へのお礼

＞korochan様
ご回答頂きありがとうございます。
３桁限定では１桁づつ確認して行い、桁が長い場合は抹消してNULLチェックで比較する。理解いたしました。早速明日実証させて頂き報告させて頂きます。ありがとうございました。

お礼日時：2006/03/20 00:20

No.5 回答者： chukenkenkou 回答日時： 2006/03/20 06:06

Oracleの経験があまりないのですが、

substr(列,i,j) between a and b

のような条件を指定した場合、インデクスは使用
できるのでしょうか？

いくつかのRDBMSを知っていますが、こういった式
（列の一部を関数で切り出す）を書くと、インデクス
が利用できないものが少なくありません。

No.4 回答者： g_express999 回答日時： 2006/03/20 00:40

＞＃１どの

別名に対する条件(where)記述は、文法エラーになるはずです。
また、文法エラーを回避しても、"1o9"のような値が範囲にマッチしますので
比較の考え方も再考の余地ありです。

確かにその通りですね。失礼しました（＾＾；ヾ

No.2 回答者： g_express999 回答日時： 2006/03/20 00:04

＃１です。

英字が含まれている場合はどのように書いたらよろしいでしょうか・・・。

上３桁に英字が含まれてるもののみを抽出するっていう意味ですか？
だとしたら、＃１の回答が正しければ、その逆の条件で抽出してあげればよいので、

select
TEST as TEST
substr(TEST,1,3) as TEST_W
from Aテーブル
where
TEST_W < '000'
or TEST_W > '999'

結局、CHR型の大小比較って文字コードでの大小比較だと思うので。

なお、＃１の回答でfrom句が抜けてました。すみません。

この回答へのお礼

g_express999様、早々のご回答ありがとうございます。
文字コードの比較になるんですね。勉強不足です。
Access2003ではIsNumeric(left(TEST),3)で書いてうまくいったので、IsNumericを意識していろいろやったのですがオラクルではうまくいかなかったのです。
とても参考になるお答えを頂きまして大変ありがとうございます。
明日、実証してご報告させて頂きます。
ありがとうございました。

お礼日時：2006/03/20 00:14

No.1 回答者： g_express999 回答日時： 2006/03/19 23:38

たとえば、

select
TEST as TEST
substr(TEST,1,3) as TEST_W
where
TEST_W between '000' and '999'

ではダメですか？

この回答へのお礼

早々のご回答ありがとうございます。
今の環境では実証できないのですが、構文を拝見させて頂きまして、いけそうな気がしました。さっそく明日実証し結果を報告させて頂きます。頂きました回答文では数値のパターンは判別できそうなのですが英字が含まれている場合はどのように書いたらよろしいでしょうか・・・。　お礼欄に質問を追加するような事を書きまして、大変恐縮です。すみません。

お礼日時：2006/03/19 23:47