4

SQLである単語を含み、その単語の大文字小文字、全角半角、空白の有無の差を無視したlikeのような絞り込みをしたいです。どのようにSQL文を書けばいいでしょうか?


ある単語:ABC

以下すべてヒットするデータの例
ABC
aBC
abc
ABC(全角)
abc (全角)
Abc (全角半角大文字小文字混合)
xxxabcyyy (abcを含む)
a b c(間に空白有)

ヒットしないデータの例
abdc

Jogenara
  • 622
  • 10
  • 18
  • 1
    mysqlやoracleなど、想定しているデータベースは何でしょうか? – payaneco Jul 08 '22 at 00:56
  • 可能ならどのDBでも使える手法が良いですが、とりあえずMS SQLServer を想定してください。 – Jogenara Jul 08 '22 at 03:54
  • 1
    SQL Server ならフルテキスト検索機能があります。機能が追加された当時に記事を見ただけなので把握はしていないのですが。https://docs.microsoft.com/ja-jp/sql/relational-databases/search/full-text-search?view=sql-server-ver16 – KOZ Jul 08 '22 at 07:22

2 Answers2

5

まず、MySQL や PostgreSQL などの RDBMS で恒常的にこのような検索をするのはあまりオススメしません。頑張ればできないこともないですが、クエリの実行にインデックスを使いづらいため基本的にフルスキャンになってしまいスロークエリになってしまいがちです。Elasticsearch など全文検索用のソフトウェアを使うのをオススメします。

「頑張ればできないこともない」というのは、たとえば正規表現でマッチするようにするとある程度ご所望の検索ができます。

例として MySQL だと、'ABC' という文字列から '[aAaA][[:blank:]]*[bBbB][[:blank:]]*[cCcC]' という正規表現を作って regexp 演算子でマッチを計算するとある程度は検索できます。ただし MySQL のバージョンによっては多バイト文字が入っていると予想しづらい挙動になる場合があります。マニュアルをご覧ください。

※もしユーザー入力を元に動的に検索するのが必要なのであれば、ReDoS に繋がらないかどうかのチェックも必要でしょう。

また他に考えられる方法として、RDBMS によっては全文検索用の機能が用意されていることがあるので、これが利用できる場合があるかもしれません。たとえば MySQL には FULLTEXT インデックスがあります。ただ、スペースを無視してマッチしたいとなるとそのままは利用できなさそうなのと、またもし日本語を含む場合はトークン化が難しいので ngram パーサーで頑張ることにするなどの工夫が必要になりそうです。

nekketsuuu
  • 23,683
  • 11
  • 50
  • 115
3

SQL ServerならばCOLLATEを使って照合順序を指定し、likeで大文字小文字や全角半角の区別を有効/無効にすることができます。
(ちなみにSQL Serverの標準設定で大文字小文字、全角半角の区別はしないようです)

途中のスペースを区別しない照合順序はないので、replace関数を使ってスペースを取り除くことによって(インデックスは犠牲になるかもしれませんが)目的は達成できます。

select t.my_text
from  (select 'ABC' my_text union all
       select 'aBC' my_text union all
       select 'abc' my_text union all
       select 'ABC' my_text union all --(全角)
       select 'abc' my_text union all --(全角)
       select 'Abc' my_text union all -- (全角半角大文字小文字混合)
       select 'xxxabcyyy' my_text union all -- (abcを含む)
       select 'a b c' my_text --(間に空白有)
      ) t
where replace(replace(t.my_text, ' ', ''), ' ', '') like '%abc%' COLLATE Japanese_CI_AI

なお、他のデータベースでも正規表現を使って類似の処理を実現できなくもないことは @nekketsuuu さんの回答の通りですが、SQL規格に正規表現はなく、それぞれのデータベースの拡張機能として実装されています。
そのため、データベース共通で使えるあいまい検索のSQLは現時点で存在しないはずです。

payaneco
  • 12,323
  • 21
  • 57
  • CLLATE よさそうです。SQL Serverでしかつかえないのはちょっと引っ掛かりますが、これで急場をしのげます。ありがとうございます。 – Jogenara Jul 09 '22 at 01:13