特定の文字列が含まれているか調べる

質問

すいません、先ほど「条件が複雑なSQL文」の質問を立てさせてもらったのですが、
あとから自分で考えてもおかしな（どうやっていいのか分からないというか・・・）
部分がありましたので、今回はそれよりは簡潔（？^^;））な内容の質問なのですが、
例えば、「abcd」という文字列が、あるテーブルのnameに含まれてるかを調べたいとします。
そしてマッチした割合やあと何文字でマッチするか？のデータも欲しいのです。
この場合のSQL文はどのように記述すれば良いのでしょうか？

【調べる文字列】
abcd

[tbl]
id　　name
1　　　ab
2　　　abcd
3　　　abcdefgh
4　　　xyz

【得たい結果（マッチした割合％の降順）】
[tblのid]　[nameにマッチした割合％（パーセント）]　[nameにあと何文字でマッチするか？]
　　2　　　　　　　　　　100　　　　　　　　　　　　　　　　　　　　　　　　0
　　3　　　　　　　　　　　50　　　　　　　　　　　　　　　　　　　　　　　　4

回答No.8

kosukejlampnet · ベストアンサー

SQLできました。

<?php
$str = "abc";
$len = strlen($str);

$whereStr = array();
$chunk = array();

for ( $i=0;$i<$len;$i++ ) { // 開始位置
$chunk[] = substr($str, $i, 1);
}

$array = array_count_values($chunk);

foreach ( $array as $key=>$val ) {
$whereStr[] = "(SELECT `id` , `name`, {$val} as `match_count` FROM `tbl` WHERE `name` REGEXP '[{$key}]{{$val}}')";
}

$count = count($whereStr);
$where = implode(" UNION ALL ", $whereStr);

$sql = "SELECT
`id`
, `name`
, LENGTH(`name`)-SUM(`match_count`) as `rest`
, SUM(`match_count`) / LENGTH(`name`) * 100 as `percent`
FROM ({$where}) UT
GROUP BY `id`
HAVING SUM(`match_count`)=LENGTH('{$str}')
ORDER BY `id`";
//ご自身の環境に合わせてください
$cn = mysql_connect('localhost', 'root', 'password');

mysql_select_db('test', $cn);
$res = mysql_query($sql, $cn) or die(mysql_error());
$row = mysql_fetch_assoc($res);

do {
echo print_r($row, 1) . '<br />';

} while($row = mysql_fetch_assoc($res));

?>

お礼

ご回答ありがとうございます。
おお、まさしく理想とする結果が得られました。
ありがとうございます。

回答No.7

kosukejlampnet

ずっと、試行錯誤しているけど、難しいねこれ！
時間かかりそう。とりあえず、何日かは質問締めないで欲しいな。

回答No.6

yambejp

ごめんなさい、仕様を理解していなかった。
「ab」という文字群で評価をしていたので全然処理がちがいました。

一文字ずつ評価するならプロシージャで処理すればよさげですけど
それなりに面倒なので、せっかく別解がいいとこいっているようなので
そちらを応用されるとよいかと

回答No.5

kosukejlampnet

まあ、あとは好きにアレンジしてくださいな。

回答No.4

kosukejlampnet

これSQLだけで考えると難しいですね。PHPのコードで書いてみましたので参考になればと思います。

$str = "ab";
$len = strlen($str);

$chunk = array();
$whereStr = array();

for ($k=$len; $k>=1; $k--) {
for ( $i=0;$i<$len-($k-1);$i++ ) {
$chunk = substr($str, $i, $k);
$whereStr[] = "(SELECT `id`, `name`, '{$chunk}' as `str` FROM `tbl` WHERE `name` LIKE '%{$chunk}%')";
}
}

$count = count($whereStr);
$where = implode(" UNION ALL ", $whereStr);

$sql = "SELECT
`id`
, `name`
, LENGTH(`name`) - LENGTH('{$str}') as `rest`
, LENGTH('{$str}') / LENGTH(`name`) * 100 as `percent`
FROM ({$where}) UT
WHERE LENGTH(`name`) - LENGTH('{$str}') >= 0
GROUP BY `id`";
mysql_query($sql, $cn);

お礼

ご回答ありがとうございます。
なるほど、最初「ab」で試してみたら希望通りの結果になったのですが、
次に「aa」で試したら

【調べる文字列】
aa

[tbl]
id　　name
1　　　a
2　　　ab
3　　　abcd
4　　　cdefbgha
5　　　xyz

Array
(
[id] => 2
[name] => ab
[rest] => 0
[percent] => 100.0000
)
Array
(
[id] => 3
[name] => abcd
[rest] => 2
[percent] => 50.0000
)
Array
(
[id] => 4
[name] => cdefbgha
[rest] => 6
[percent] => 25.0000
)

という結果が得れたのですが、自分が求める結果としては「aa」なら
このname候補だと値を返さないという結果なのです。
最低でもnameには「a」が2文字以上あって始めて引っかかる（値を返す）
結果なのですが、そのようなことは可能なのでしょうか？

回答No.3

yambejp

どうもご利用の環境が２行以上のSQLが実行できないとか？
ちょっと工夫してみましたがどうでしょ？
#2の例をもとに・・・

//準備
create table tbl2(id int,name varchar(30));
insert into tbl2 values(1,'a'),(2,'ab'),(3,'abcd'),(4,'cdefbgha'),(5,'xyz');

//実行
select id
,length(@str) / length(name) *100 as hit
,length(name) - length(@str) as rest
from tbl
inner join (select @str:='ab' as str) as sub on
tbl.name regexp sub.str;

「@str:='ab'」の文字を変えてつかってください

お礼

ご回答ありがとうございます。

＞どうもご利用の環境が２行以上のSQLが実行できないとか？
PHPのZend Framework を使っているのですがそうかもしれないですね、、
ちょっと自分でも今のところ分からないのですが、、

なるほど、確認なのですが、次のようなSQLをセットして
試してみたのですが、これで合っていますでしょうか？

select id
,length('ab') / length(name) *100 as hit
,length(name) - length('ab') as rest
from tbl
inner join (select @str:='ab' as str) as sub on
tbl.name regexp sub.str

「ab」で試した結果なのですが、

【調べる文字列】
ab

[tbl]
id　　name
1　　　a
2　　　ab
3　　　abcd
4　　　cdefbgha
5　　　xyz

Array
(
[id] => 2
[hit] => 100.0000
[rest] => 0
)
Array
(
[id] => 3
[hit] => 50.0000
[rest] => 2
)

となって、希望通りなら

Array
(
[id] => 4
[name] => cdefbgha
[rest] => 6
[percent] => 25.0000
)

が足りないのですが、なにか自分の記述間違いがあるのでしょうか？

補足

すいません、今気づいたのですが、仰ってることの意図としてはこちら（というか
そのままで）でしょうか？

select id
,length(@str) / length(name) *100 as hit
,length(name) - length(@str) as rest
from tbl
inner join (select @str:='ab' as str) as sub on
tbl.name regexp sub.str

ただこれでも全く同じ結果なのです・・・

回答No.2

yambejp

単純に考えていいならこんな感じ

set @str='abcd';
select id
,length(@str) / length(name) *100 as hit
,length(name) - length(@str) as rest
from tbl
where name like concat('%',@str,'%');

ちなみにabに対するababはヒット率50なのでしょうか？100なのでしょうか？

お礼

ご回答ありがとうございます。
大変申し訳ないですが、No.1の返答でも言いましたが、
質問自体にミスがあったので、No.1の返答の欄を参考に
考え直して頂けないのでしょうか？お願いします。

それと

set @str='abcd';
select id
,length(@str) / length(name) *100 as hit
,length(name) - length(@str) as rest
from tbl
where name like concat('%',@str,'%')

を実行したのですがエラーになってしまいます・・・

「Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error' in 」

このようなエラー情報しかないのですが、分かりますでしょか？
「concat()」の後ろの「;」が原因なのかと思って外してみたのですが、変わらないです。。
ちなみにtblの構造は

CREATE TABLE tbl (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32)
) ENGINE=InnoDB;

回答No.1

kosukejlampnet

単純なLIKE検索では実装できないわけですね。
質問を質問で返すようで申し訳ないのですが、疑問点があるので。

疑問１
id 1 の場合、
1　　　50　　　-2
と返さなくて良いのですか？

疑問２
仮に、
id　　　　name
5　　　　ssdcbafg
というデータがあった場合は
5　　　　100　　　　4
となることを期待していますか？

お礼

ご回答ありがとうございます。
すいません、何度も大変申し訳ないですが、
今、No.2のyambejpさんの
＞ちなみにabに対するababはヒット率50なのでしょうか？100なのでしょうか？
に対する返答を書いてて気が付いたのです、自分が質問した例は逆でした・・・
つまり【調べる文字列】が「ab」の場合でした・・・

【調べる文字列】
ab

[tbl]
id　　name
1　　　a
2　　　ab
3　　　abcd
4　　　cdefbgha
5　　　xyz

【得たい結果（マッチした割合％の降順）】
[tblのid]　[nameにマッチした割合％（パーセント）]　[nameにあと何文字でマッチするか？]
　　2　　　　　　　　　　100　　　　　　　　　　　　　　　　　　　　　　　　0
　　3　　　　　　　　　　　50　　　　　　　　　　　　　　　　　　　　　　　　2
　　4　　　　　　　　　　　25　　　　　　　　　　　　　　　　　　　　　　　　6

もう一度質問を立て直したい気分なのですが、そうもいかないので
申し訳ないのですが、この例で考えて頂けないでしょうか？

＞疑問１
なるほど、たしかに言われてみればそれもあった方がなにかといいのかなぁと
思いはじめた^^;）のですが、そうですね、とりあえず今のところは返さなくていいです。

みなさんから疑問が出てくるのは当然でしたね・・・
もう一度これでお願いします。。。