MySQLのSELECT文の条件で記号が含まれた値をヒットさせたい

Question

MySQLの条件定義で表題の件が可能なのかどうか、調べたのですがわからなかったのでご教授いただきたいです。

#やりたいこと
下記のような値が格納されたDBがあります

tb_actor

no	name
1	ジョニー・デップ
2	オーランド・ブルーム
3	ハリー・ポッター

「ジョニー」とか「デップ」などで検索された時はLIKE検索で1のレコードをヒットすることはできるのですが「ジョニーデップ」と入力があった時に記号の「・」があるため、ヒットさせることはできません。

SELECT * FROM tb_actor WHERE name LIKE '%ジョニーデップ%';

※記号は・だけでなく一般的な記号全般（$#_-*など）を含みます。

そもそも記号が含まれている値に対して記号無しのワードでLIKE検索はできないのでしょうか。

表題の件、できる・できないを含めてご教授いただけないでしょうか。

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

Answer 1

たとえば「ジョニー・デップ」で「ジョ・ニーデップ 」までヒットして良いかという問題です。
問題ないならreplaceを利用して「ジョニーデップ 」に加工した後、
ヒットさせればよいですが、インデックスなど効かないのであまり
効率的な検索にはなりません。

追記

create table tb_actor(no int unique,name varchar(30));

insert into tb_actor values 
(1,'ジョニー・デップ'),
(2,'ジョニーデップ'),
(3,'ジョニー/デップ'),
(4,'ジョ#ニーデップ'),
(5,'ジ__・--ョ$ニ#$$ー_デ-ッ******プ'),
(6,'ジョ・ニーデップ'),
(11,'オーランド・ブルーム'),
(21,'ハリー・ポッター');

として、「・#_-$*」を除く場合こんな感じ

select no
,name from tb_actor
where 
replace(
replace(
replace(
replace(
replace(
replace(name,'・','')
,'$','')
,'#','')
,'_','')
,'-','')
,'*','') like '%ジョニーデップ%'

function

汎用性のためfunctionを使います
まずストアードファンクション（replace2）を作成します

drop function if exists replace2;
delimiter //
create function replace2(str varchar(255), pattern varchar(20), replacement varchar(20)) returns varchar(255) DETERMINISTIC
begin
DECLARE x INT DEFAULT 0;
DECLARE i INT DEFAULT 1;
set x=length(pattern);
while i<=x
do
set str=replace(str,substr(pattern,i,1),replacement);
set i=i+1;
end while;
return str;
end
//
delimiter ;

functionを利用した実行

select no
,name from tb_actor
where replace2(name,'・$#_-*','') like '%ジョニーデップ%';

Comments

[Yossyu]

ありがとうございます。

曖昧なヒット重視なので、極端な話であれば

「ジョニーデップ」という検索ワードに対して
「ジョニー・デップ」でも「ジョ・ニーデップ」でも「ジョニーデッ・・プ」でもヒットしても構わないです。

replaceも考えたのですが、記述方法がわかりませんでした。
ご教授いただけないでしょうか。

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

[Yossyu]

ありがとうございます！
その記述はまったく思いつかなかったです。

replaceに関しては記号の数だけ入れ子にしていくしか方法はなさそうでしょうか。
実際に記号が20個あれば20回、なおかつ対象がname以外もあればそれら全てに同じ記述が必要でしょうか。

[yambejp]

functionを利用したサンプルを挙げておきます。
mysqlのfunction指定は若干なれていないとわかりづらいかもしれません

[Yossyu]

ありがとうございます！

mysqlのfunctionは初めて目にしました。。。
貴重なアドバイスありがとうございます。

内容を把握するのになかなか難しそうですが実際に動かしてみて
理解したいと思います。

ありがとうございます！