WHERE句を使って検索をする際に、特定の文字を含んだ条件にしたい場合はないでしょうか。もしくは特定の文字で始まるキーワードで検索したいといったケースもあるでしょう。
これらを実現するのが「あいまい検索」と呼ばれる方法です。SQLではLIKE演算子を使うことであいまい検索ができます。
本記事ではあいまい検索とは何か、LIKE演算子とは何かをお伝えします。そして、あいまい検索の種類でもある前方一致検索、後方一致検索、部分一致検索についてケーススタディを交えて詳しく解説しています。
目次
あいまい検索とは
あいまい検索とは検索の方法を指す用語の一つで、指定した検索キーワードを含むデータの検索です。
例えば、下記の苗字の人がいたとします。
- 山田
- 佐藤
- 山本
- 丸山
- 木下
この中から苗字に「山」が含まれている人を抽出すると下記になります。
- 山田
- 山本
- 丸山
このように特定のキーワードで検索することがあいまい検索です。
SQLであいまい検索をするためには「LIKE演算子」を使用します。LIKE演算子について詳しく見ていきましょう。
LIKE演算子とは
LIKE演算子を使うと特定のカラムに対して文字検索をすることができます。実際にデータ分析をする際、頻繁に使う文法です。
基本的な使い方
下記のようにクエリを書くことで、指定したカラムが◯◯という文字を含んだレコードという条件にできます。
SELECT カラム名
FROM テーブル名
WHERE カラム名 LIKE '検索文字';ただし、この書き方ではクォーテーションで囲まれた文字と完全一致の条件になってしまいます。これでは、単にWHERE条件で検索するのと変わりません。
特定の文字が一致することを条件とする部分一致を実現するには、下記のように書きます。
SELECT カラム名
FROM テーブル名
WHERE カラム名 LIKE '%検索文字%';これで特定の検索文字を含んだカラムのレコードを取得するというクエリになりました。
さて、%という見慣れない記号が出てきましたね。この記号のことをワイルドカードと言います。
ワイルドカードとは
コンピューターの世界において、ワイルドカードはどんな文字列にも一致することを指す記号を意味します。つまり、何にでもなり得る文字
トランプをイメージすると分かりやすいでしょう。大富豪をする時、ジョーカーはどんなカードにもなり得るためワイルドカードに該当します。（大富豪知らない人はすみません…）
SQLにおけるワイルドカードは下記2種類あります。
| ワイルドカード | 意味 |
|---|---|
| % | 0文字以上の任意の文字列 |
| _ | 任意の1文字 |
例えば、'%山田%'とすれば、名前のどこかに山が含まれている人を抽出することができます。一方、'_山%'とした場合、任意の1文字が先頭に必要になるため、丸山はOK、山田はNGになります。
なんとなく使い方が理解できたのではないでしょうか。
LIKE演算子を使ってあいまい検索をする場合、基本的にはワイルドカードをセットで使うことが多いので覚えておきましょう。
ここからは、あいまい検索の種類である「前方一致」「後方一致」「部分一致」の使い方をそれぞれ見ていきます。
前方一致で検索する
まず最初は前方一致で検索するやり方です。前方一致は検索語から始まる単語に一致している文字を検索します。
下記にサンプルデータとしてrecipeテーブルを用意しました。
| id | name |
|---|---|
| 1 | ポテトチップス |
| 2 | フライドポテト |
| 3 | ポテトサラダ |
| 4 | ジャーマンポテト |
| 5 | 明太子とポテトのチーズ焼き |
この中から「ポテト」で始まるレシピを前方一致で取得します。
SELECT *
FROM recipe
WHERE name LIKE 'ポテト%';ポテトの後方にだけ%を付けていることが分かります。これで「ポテトから始まる任意の文字列」という条件指定をすることができました。
実行結果は下記になります。
| id | name |
|---|---|
| 1 | ポテトチップス |
| 3 | ポテトサラダ |
文頭に「ポテト」が含まれる文字列のみ取得することができました。
後方一致で検索する
次に後方一致で検索するやり方を見ていきます。後方一致は検索語で終わる単語に一致している文字を検索します。
今回は「ポテト」で終わるレシピを後方一致で取得します。
SELECT *
FROM recipe
WHERE name LIKE '%ポテト';ポテトの前方にだけ%を付けていることが分かります。これで「ポテトで終わる任意の文字列」という条件指定をすることができました。
実行結果は下記になります。
| id | name |
|---|---|
| 2 | フライドポテト |
| 4 | ジャーマンポテト |
文末に「ポテト」が含まれる文字列のみ取得することができました。
部分一致で検索する
次に部分一致で検索するやり方です。部分一致は任意の場所に検索語が含まれる単語に一致している文字を検索します。
今回は「ポテト」が含まれるレシピを部分一致で取得します。
SELECT *
FROM recipe
WHERE name LIKE '%ポテト%';ポテトの前方と後方の両方に%を付けていることが分かります。これで「ポテトを含む任意の文字列」という条件指定をすることができました。
実行結果は下記になります。
| id | name |
|---|---|
| 1 | ポテトチップス |
| 2 | フライドポテト |
| 3 | ポテトサラダ |
| 4 | ジャーマンポテト |
| 5 | 明太子とポテトのチーズ焼き |
「ポテト」の文字が含まれているレシピを取得することができました。今回の場合、全てということになります。
条件を絞った部分一致で検索する
さらに条件を絞った部分一致のケースも見てみましょう。
ポテトの後に任意の3文字が含まれるレシピを取得します。つまり、「ポテト◯◯◯」というレシピだけですね。
SELECT *
FROM recipe
WHERE name LIKE 'ポテト___';ポテトの後方にワイルドカード_を3個付けています（ちょっと分かりにくいですが…）。これで、「ポテトの後に任意の3文字を含む文字列」という条件を指定することができました。
実行結果は下記になります。
| id | name |
|---|---|
| 3 | ポテトサラダ |
「ポテト◯◯◯」に該当するポテトサラダが取得できました。
否定検索「NOT LIKE演算子」
LIKE演算子には否定形も用意されており、それが「NOT LIKE演算子」です。LIKE演算子の前にNOTを付けるだけで検索条件はこれまで説明してきた内容と変わりません。
基本形は下記です。
SELECT カラム名
FROM テーブル名
WHERE NOT LIKE '検索文字';今回はNOT LIKE演算子を使って、文頭がポテトで始まらないレシピを取得してみます。
SELECT *
FROM recipe
WHERE NOT LIKE 'ポテト%';ポテトの後方に%を付けて、「ポテトで始まる文字列」という条件を指定し、NOTによってそれを否定しています。
実行結果は下記になります。
| id | name |
|---|---|
| 2 | フライドポテト |
| 4 | ジャーマンポテト |
| 5 | 明太子とポテトのチーズ焼き |
ポテトで始まらないレシピを取得することができました。
内容のまとめ
- あいまい検索は、指定した検索キーワードを含むデータの検索のこと
- SQLであいまい検索をする場合はLIKE演算子を使う
- SQLにおけるワイルドカードは2種類あり、
%と_ - 前方一致で検索する方法は
'文字列%' - 後方一致で検索する方法は
'%文字列' - 部分一致で検索する方法は
'%文字列%'
