公開日：2019/10/3
更新日：2019/10/3
キーワード：曖昧検索 正規表現
文字数：4900（読み終わるまでおよそ8分）
この記事でわかること
- SQL文法におけるLIKEを使用した曖昧検索
- SQL文法における正規表現による検索方法
はじめに
今回はSQL文法において、LIKEを使用した『曖昧検索』と、『正規表現による検索方法』について説明していきたいと思います。
曖昧検索とは、任意の文字列が含まれるレコードを取得したい場合に用いる検索手法の1つです。
例えば「あいうえ」という文字が含まれているレコードを取得したい場合に使用します。
正規表現とは、文字列の集合を一つの文字列で表現する方法の1つです。
例えば、「abcd…..xyz」という小文字アルファベットの集合を、簡単な文字でまとめて表現することができます。
この手法を用いた柔軟な検索も可能です。
それは後々説明しますので、ぜひご覧になってください。
1. 曖昧検索について
ここでは曖昧検索について説明していきます。
1) 「LIKE」の基本的な使い方
曖昧検索を行うにはLIKE演算子が不可欠です。
記述の方法は、下記の通りです。
2) ワイルドカード文字とは
ワイルドカード文字とは、SQL文法においてLIKE演算子のうしろに記述するものであり、『%』と組み合わせて使われます。
ワイルドカード文字には、「前方一致」、「後方一致」、「部分一致」があります。それらを表現するには、『%』を挿入する位置が重要になってきます。
| 一致の種別 | 表記 |
| 前方一致 | △△△% |
| 後方一致 | %△△△ |
| 部分一致 | %△△△% |
※△△△は任意の文字列
このように、後ろに%をつけると前方一致、前に%をつけると後方一致、前と後ろの両方につけることで部分一致となります。
3) 実際にやってみよう
では、実際にLIKE演算子とワイルドカード文字を使って抽出をしてみましょう。
例として、下記のようなテストテーブルが登録されているとします。
□テストテーブル
| 氏名 |
| 山田太郎 |
| 今野五郎 |
| 佐藤太一 |
| 佐田拓海 |
①条件：氏名が「佐」で始まるデータのみを取得する（前方一致）
このSQLを実行した場合、取得できるデータは以下の通りです。
| 氏名 |
| 佐藤太一 |
| 佐田拓海 |
②条件：氏名が「郎」で終わるデータのみを取得する（後方一致）
このSQLを実行した場合、取得できるデータは以下の通りです。
| 氏名 |
| 山田太郎 |
| 今野五郎 |
③条件：氏名に「田」を含んでいるデータのみを取得する（部分一致）
このSQLを実行した場合、取得できるデータは以下の通りです。
| 氏名 |
| 山田太郎 |
| 佐田拓海 |
4) 曖昧検索の否定について（NOT LIKEについて）
「～～ではない」という否定をあらわす演算子に、「NOT演算子」というものがあります。
これとLIKE演算子を組み合わせることもできます。
前回と同様、テストテーブルが登録されている前提で説明していきます。
□テストテーブル
| 氏名 |
| 山田太郎 |
| 今野五郎 |
| 佐藤太一 |
| 佐田拓海 |
①条件：氏名が「佐」で始まらないデータのみを取得する（前方一致の否定）
このSQLを実行した場合、取得できるデータは以下の通りです。
| 氏名 |
| 山田太郎 |
| 今野五郎 |
②条件：氏名が「郎」で終わらないデータのみを取得する（後方一致の否定）
このSQLを実行した場合、取得できるデータは以下の通りです。
| 氏名 |
| 佐藤太一 |
| 佐田拓海 |
③条件：氏名に「田」を含まないデータのみを取得する（部分一致の否定）
このSQLを実行した場合、取得できるデータは以下の通りです。
| 氏名 |
| 今野五郎 |
| 佐藤太一 |
2. 完全一致について
ここまでは曖昧検索について説明しましたが、完全一致検索についても触れておきます。
完全一致検索とはその名の通り、指定した文字列をそのまま検索する手法です。
基本的に検索といったらこちらが主流です。
下記のように書きます。
「=」なら完全一致、「LIKE」なら曖昧検索と覚えておけばよいでしょう。
捕捉として、LIKEのあとに記述するワイルドカード文字列に%を付与しなかった場合、それは完全一致検索と同等扱いとなります。
しかし、可読性が悪くなりますし、パフォーマンスが落ちる可能性もあるので避けたほうが良いでしょう。
3. 大文字・小文字を区別する方法(BINARY演算子について）
データベースの1つに「MySQL」があります。
このMySQLはデフォルトの設定で検索のさい、大文字・小文字を区別しないで抽出をしてしまいます。
例えば、「abcd」というデータを抽出したい場合、「aBcD」とか「AbCd」も取得できてしまいます。
ここでは大文字・小文字を区別してくれるBINARY演算子を紹介します。
もしくは
と記述することにより、大文字・小文字を判別して検索を行うことができます。
4. 正規表現を用いた条件抽出について
LIKE演算子を用いた曖昧検索よりも、さらに細かい検索条件を設定することができます。
それが、正規表現を使った検索です。
ここからは正規表現について詳しく説明していきます。
1) 正規表現とは
正規表現とは、最初にも軽く述べましたが「文字列の集合を一つの文字列で表現する方法の一つ」です。
主に、テキストエディタ、ワードプロセッサなどのアプリケーションで、マッチさせるべき対象を表すために用いられる表現です。
ほとんどのプログラミング言語では、ライブラリによって正規表現を使うことができます。
そしてそれは、SQL文法においても同様です。
2) 正規表現で使われる基本的な記号
では、正規表現とはどのように記述されるか見ていきましょう。
①いずれか１文字にマッチする文字を抽出する（[…]について）
[…]（…の部分は任意の文字）という表現方法があります。これは、かっこ内に含まれるいずれか１文字にマッチする文字を抽出する場合に使われます。
例をあげてみていきましょう。
| 対象文字の集合 | 正規表現 | マッチする文字 |
| abcdefghijklmn | [abc] | a, b, c |
| [a-h] | a, b, c, d, e, f, g, h |
対象となる文字は、a～nまでのアルファベットとします。
[abc]と記述すると、対象となる文字の集合から、a、b、c、いずれかの文字にマッチするものを抽出します。ここでは3つの文字全てが集合のなかに含まれていましたので、マッチする文字は『a, b, c』となります。
また、[a-h]という記述はアルファベットa～hのあいだで、いずれかの文字にマッチするものを抽出します。
マッチする文字は『a, b, c, d, e, f, g, h』となり、i～nまでのアルファベットはマッチしないで抽出されません。
②任意の文字以外にマッチする文字を抽出する（[^…]について）
[^…]（…の部分は任意の文字）という正規表現は、かっこ内に含まれる文字以外にマッチする文字を抽出する場合に使われます。下記、例となります。
| 対象文字の集合 | 正規表現 | マッチする文字 |
| abcdefghijklmn | [^abc] | d, e, f, g, h, i, j, k, l, m, n |
| [^a-h] | i, j, k, l, m, n |
③直前の文字を0回以上繰り返す文字を抽出する（*について）
文字列の途中に「*」を挿入することにより、*の直前に書かれた文字を0回以上繰り返す文字を抽出対象とします。
分かりやすく例をあげてみます。
| 対象文字の集合 | 正規表現 | マッチする文字 |
| apple apppple aple ale | ap*le | apple apppple aple ale |
ap*leと書くことにより、*の直前に書かれたpが繰り返す対象となります。
また、この場合は0回以上なので、pが含まれていなくても構いません。
なので、マッチする文字のなかに『ale』も含まれているのです。
④直前の文字を1回以上繰り返す文字を抽出する（+について）
文字列の途中に「+」を挿入することにより、+の直前に書かれた文字を1回以上繰り返す文字を抽出対象とします。
分かりやすく例をあげてみます。
| 対象文字の集合 | 正規表現 | マッチする文字 |
| apple apppple aple ale | ap+le | apple apppple aple |
ap+leと書くことにより、+の直前に書かれたpが繰り返す対象となります。
1つ前に説明した「*」との違いは、0回以上繰り返すか、1回以上繰り返すかどうかです。
つまり、pは必ず1回は登場しないとマッチする文字の対象としてみなされません。
なので、今回はマッチする文字のなかに『ale』は含まれていません。
⑤直前の文字が0個、もしくは1個の文字を抽出する（?について）
文字列の途中に「?」を挿入することにより、?の直前に書かれた文字が0個、もしくは1個の文字を抽出対象とします。
分かりやすく例をあげてみます。
| 対象文字の集合 | 正規表現 | マッチする文字 |
| apple apppple aple ale | ap?le | aple ale |
?の直前に書かれたpが0個、または1個ということなので、マッチする文字は『aple』と『ale』です。
他の文字はpが2個以上存在するので、対象外となります。
⑥いずれかの条件(OR条件)に合致する文字を抽出する（|について）
文字列を「|」で連結することにより、それらをOR条件として扱うことができます。
例をあげます。
| 対象文字の集合 | 正規表現 | マッチする文字 |
| apple appel appee appll appoo | app(le|el|oo) | apple appel appoo |
app以降の「le」、「el」、「oo」を「|」で連結してOR条件としています。
なので、マッチする文字は上記の3つとなります。
⑦直後の文字が対象の先頭にある文字を抽出する（^について）
文字列の前に「^」を書くことにより、それが先頭にある文字を抽出することができます。
例をあげて説明します。
| 対象文字の集合 | 正規表現 | マッチする文字 |
| applemac applestore iphoneapple | ^apple | applemac applestore |
「apple」という文字が先頭にあるのは「applemac」と「applestore」ですので、この2つがマッチする文字となります。
⑧直前の文字が対象の末尾にある文字を抽出する（$について）
文字列の後ろ に「$」を書くことにより、それが末尾にある文字を抽出することができます。
例をあげて説明します。
| 対象文字の集合 | 正規表現 | マッチする文字 |
| applemac applestore iphoneapple | apple$ | iphoneapple |
「apple」という文字が末尾にあるのは「iphoneapple」だけですので、これがマッチする文字となります。
5. 正規表現をSQL文法に組み込んでみる
これまで説明してきた正規表現をSQLの文に組み込んでみましょう。
データベースの種類によりSQLの記述方法が違うので、ここではオープンソースで有名な「MySQL」と「PostgreSQL」の2つについて触れておきます。
まず、データベースに「電話番号テーブル」が登録されているとします。
下記のようなデータとします。
□電話番号テーブル
| 電話番号 |
| 03-1234-5678 |
| 0120-123-123 |
| 0800-123-123 |
| 050-0800-0120 |
また、SQL文を下記のように記述します。
- MySQL
- PostgreSQL
正規表現を検索文字列として扱う場合、MySQLはREGEXP演算子を使います。
また、PostgreSQLでは「~」を使用します。
今回使った正規表現は、前述⑦で説明した前方一致の「^」と、⑥で説明したOR条件である「|」との組み合わせです。
『0120、または0800で始まる文字列を抽出する』という意味の正規表現になります。
このSQLを実行した場合、取得できるデータを以下の通りです。
| 電話番号 |
| 0120-123-123 |
| 0800-123-123 |
6. おわりに
SQL文法において、データベースから検索を行い、データを取得するという行為はすごく一般的であり、そして技術的にハマりやすいところであります。
なので、このページで説明した「完全一致検索」、「曖昧検索」そして「正規表現を用いた検索」などを理解するのはとても重要です。
このページを見てくださった皆様の技術的な助けになれば幸いです。
piponではエンジニアの皆様に業務委託や副業でAI・データサイエンスの案件をご紹介しています！
piponの案件にご興味がある方は以下のフォームにご登録ください。案件をご案内します。