sql 検索条件を上3桁一致で検索するには？

Question

sqlにて
abcテーブルの
datasカラムに
12桁の数値が入っています。
上3桁が991以外のランダム
上3桁が991
のデータがあり

sql文にて
上3桁が991のデータを取得
上3桁が991以外のデータを取得
する方法を教えてください。
where句での操作にはなると思うのですが
、、

Comments

[A.Ichi]

「12桁の数値が入っています。」と言われているので12桁以外は無いと思われます。datasの属性が不明です、bigintでしょうか。

Answer 1

「上3桁が991」の定義が曖昧

常識的に考えれば991000000000~991999999999かと思いますが
991や9911なども「上3桁が991」に当てはまります

またdatasカラムのデータ型の定義も必要です

#sample
質問者さんは要件定義をきちんとする癖をつけた方がよいですね。
中途半端な定義はバグの温床になります。

サンプルつけておきます

create table abc(datas bigint(12) unsigned zerofill,index(datas));
insert into abc values(1),(2),(991),(990000000000),(991000000000),(991000000001),(991000000002);
create table xyz(datas bigint(12),index(datas));
insert into xyz values(1),(2),(991),(990000000000),(991000000000),(991000000001),(991000000002);

zerofillしているかどうかでlikeで取れるかが異なります
zerofillしていないxyzは991などもヒットします

select * from abc where datas like '991%';
select * from xyz where datas like '991%';

数値の抽出にlikeを使うとindexの利用効率が変わります
可能な限り数値の範囲指定するのが妥当です。

一応以下のような書き方もあります

select * from abc where 991=truncate(datas / power(10,9),0)

※9というのは12桁から991=3桁を引いた数です。
ただし、検索の際にカラムを利用した計算が発生するとやや効率がさがります。

一般に数値の範囲はbetweenを利用します
数値の範囲ならabcとxyzで結果の相違はありませんのでabcのみ記載します。

select * from abc where datas between 991000000000 and 991999999999;

ここで、わざわざ数値を手でいれるのも馬鹿らしいので、
「991」と「12桁」という値を与えて計算させてみます

set @a=991,@b=12;
select * from abc where datas between @a*power(10,@b-ceiling(log10(@a))) and (@a+1)*power(10,@b-ceiling(log10(@a)))-1

これを1文で書くと以下になります。
ただし、MySQLの場合おなじSQL文中で変数の参照される範囲が定義されていませんので
正しく範囲が指定できない可能性もありますが、ほぼ問題ないでしょう。

select * from abc where datas between (@a:=991)*(@b:=power(10,12-ceiling(log10(@a)))) and (@a+1)*@b -1

Comments

[amaguri]

すみません日本語が悪かったのでしょうか？
上3桁というのは
一番上から3桁という意味だと思っていましたが、、違うのですか、、

[yambejp]

では991や9911もヒットさせるということでよいのですね？
もしくはdatasの範囲が100000000000~999999999999であるなどの
提示が必要です

[amaguri]

991は
一番上からの上3桁になります。
なので
991〇〇〇〇
にあてはまれば検索ヒットになります。
また桁は12桁と決まっています

[yambejp]

範囲指定のsampleつけておきました

[amaguri]

なるほどありがとうございます！！わかりやすかったです！

Answer 2

上3桁が991のデータを取得

select datas from abc
where datas >= 991000000000
  and datas < 992000000000

上3桁が991以外のデータを取得

select datas from abc
where datas < 991000000000
   or datas >= 992000000000

Comments

[amaguri]

すみません動かないのですが何が原因でしょうか？
シングルコーテーション必要でしょうか？

[tkturbo]

「数値」ならシングルクォーテーション不要です。
「数字」ならシングルクォーテーションが必要です。
自己解決された回答を見るに対象のデータは「数字」だったのではないですか？

[amaguri]

なるほど！
数字と数値とで違うのですね！
ありがとうございます！

Answer 3

12桁の数値が入っています。

これは例えば000000000991みたいな先頭が0になってしまうケースは存在せず、
100000000000 ~ 999999999999の範囲を指していますか？
そうであればtkturbo さんの仰るやり方が最善です。

一撃限りで実行速度を気にしないのであれば、
int(20)指定のカラムもlikeを使って暗黙的に型変換させれば意図通り取得できます（確認済みです）

select datas
from abc
where datas like '991%'

もし取りうる範囲が0 ~ 999999999999かつ、速度を気にするのであれば少し面倒です。
一例としてはこんな感じですかね？
SQL文をスクリプト言語で生成して投げてもいいかもしれません。

select datas from abc
where datas between 991000000000 and 991999999999
  or datas between 99100000000 and 99199999999
  or datas between 9910000000 and 9919999999
-- 以下省略

Comments

[amaguri]

12桁以外はありえないので
between 99100略 and 991999略

ということでいいのでしょうか？

ただこの場合
それ以外の場合通したらいいのでしょうか？

[miyabi-sun]

> ということでいいのでしょうか？
その通りです！

> それ以外の場合
全部を丸括弧で括って頭に NOT を付けます。
下記のようになります。

NOT (datas between 99100略 and 991999略 or ...)

Answer 4

where カラム名 LIKE '991%'
where カラム名 NOT LIKE '991%'
にて解決しました。

Comments

[yambejp]

私の回答にsampleつけときましたが
datasが必ず100000000000以上という条件がなければ
991などがヒットしますのでLIKEを利用する場合は気をつけてください