【SQL超入門講座】14.LIKE|部分一致や前方一致、後方一致するレコードを抽出する方法

こんにちは。キノコードです。

例えば、都道府県というカラムに、1つめのレコードに東京都、2つめのレコードに大阪府、3つめのレコードに京都府があったとします。
'府'で終わるレコードのみ抽出するにはどうしたらいいでしょうか?
また'京'が含まれるレコードを抽出すれにはどうしたらいいでしょうか?
そこで、この動画では、文字列の部分一致を条件としてデータ抽出する方法について説明します。

キノコードでは、この動画の他にも、たくさんのプログラミングのレッスンを配信しています。
チャンネル登録がまだの方は、チャンネルがどこにいったかわからなくならないように、チャンネル登録をお願いします。

また、キノコードではメンバーシップをやっています。
キノコードの動画制作活動を応援してくださる方は、月額290円から応援できます。
詳しくはチャンネル登録ボタンの隣にある「メンバーになる」をクリックしてください。
キノコードのメンバーシップの解説動画があります。

それでは、レッスンスタートです。

【SQL超入門講座】14.LIKE|部分一致や前方一致、後方一致するレコードを抽出する方法

LIKE演算子とは

LIKE演算子はSELECT文でパターンマッチングを行う際に使用します。
パターンマッチングとは複数の文字列に共通して存在するパターンを検索することです。

例として京都府、東京都、大阪府の3つの文字列を見てみましょう。
京都府、大阪府の2つには、「文字列の末尾が'府'である」というパターンが存在します。
また京都府、東京都の2つには、「文字列に'京'が含まれる」というパターンが存在します。
LIKE演算子を使うことで、こうした特定のパターンに一致するレコードを検索して抽出することができます。

SELECT文にLIKE演算子を追加した構文を確認しておきましょう。

SELECT カラム FROM テーブル
WHERE カラム LIKE パターンマッチングの条件

ワイルドカードについて

パターンマッチングはワイルドカードを用いて行います。
ワイルドカードはあらゆる文字列として扱うことのできる特殊文字のことです。
ワイルドカードには以下の2つがあります。

ワイルドカード説明
_任意の1文字の文字列
%0文字以上の任意の文字列

文字数が関係するパターンマッチングを行う場合は'_'を使います。
先ほどの例でいうと、「末尾が'府'である」というパターンは'%府'と書き表すことができます。
文字列が3文字であることも条件にするのであれば'__府'と書き表します。

ここからは実際にテーブルを用意して、パターンマッチングによる抽出を行っていきましょう。

今回は以下のordersテーブルを作成し、解説を進めていきます。
test_table03テーブルは、注文日、商品ID、商品名、サイズの4つのカラムを持つテーブルです。

注文日商品ID商品名サイズ
2021/1/1A_1000ダウンジャケットXS
2021/1/4A_1010TシャツS
2021/1/5A_2000ブルゾンM
2021/1/5A_2010カットソーL
2021/1/6B_3000ショートパンツXL
2021/1/7B_3010ロングパンツXXL
2021/1/9B_3020ジーンズXXXL

それでは、前方一致、後方一致、部分一致のパターンマッチングを順に確認していきましょう。

前方一致

まずLIKE演算子を使って前方一致のパターンマッチングを行います。
サイズが'X'から始まるレコードのみを抽出してみましょう。
ここでは全てのカラムを抽出対象とします。
SQLは次のように書きます。

SELECT * FROM orders 
WHERE サイズ LIKE 'X%'

WHERE句でパターンマッチングを行うカラムを指定しています。
続いてLIKE演算子を書き、検索するパターンを指定しています。
今回検索するのはサイズの1文字目が'X'というパターンでした。
1文字目が'X'であれば、2文字目以降にどんな文字が何文字あっても構いません。

指定するパターンに関わらない文字列はワイルドカードで置き換えます。
任意の複数の文字列を置き換えるので'%'を使いましょう。
こうして1文字目が'X'というパターンを'X%'と書き表すことができました。

それではSQLを実行して、サイズが'X'から始まるレコードのみ抽出できているか確認しましょう。

注文日商品ID商品名サイズ
2020/1/1A_1000ダウンジャケットXS
2020/1/6B_3000ショートパンツXL
2020/1/7B_3010ロングパンツXXL
2020/1/9B_3020ジーンズXXXL

サイズが'X'から始まるレコードのみ抽出できていることが確認できました。

もしサイズが「'X'から始まり、かつ2文字である」というパターンを検索するのであれば、使用するワイルドカードを変更します。
任意の1文字を示すワイルドカードは'_'でした。
SQLは次のように書きます。

SELECT * FROM orders  
WHERE サイズ LIKE 'X_'

実行結果を確認します。

注文日商品ID商品名サイズ
2020/1/1A_1000ダウンジャケットXS
2020/1/6B_3000ショートパンツXL

実行結果からサイズが’XXL’、'XXXL'のレコードが外れました。
このように文字数を検索するパターンの条件に含めるのであれば、ワイルドカードは任意の1文字を表す'_'を使用するようにしましょう。

後方一致

次にLIKE演算子を使った後方一致のパターンマッチングを行います。
前方一致と考え方は同じなので、さくっと確認していきます。
それではサイズの末尾が'L'で終わるレコードのみを抽出してみましょう。

SQLは次のように書くことができます。

SELECT * FROM ordes 
WHERE サイズ LIKE '%L'

今回検索するのはサイズの末尾が'L'であるパターンです。
それより前はどんな文字が何文字あっても構いません。
指定するパターンに関わらない部分はワイルドカードで置き換えます。
複数の文字列はワイルドカードの%を使って置き換えることができました。

このようにして、サイズの末尾が'L'で終わるというパターンを'%L'と書き表すことができました。
ではSQLを実行してみましょう。

注文日商品ID商品名サイズ
2020/1/5A_2010カットソーL
2020/1/6B_3000ショートパンツXL
2020/1/7B_3010ロングパンツXXL
2020/1/9B_3020ジーンズXXXL

サイズが'L'で終わるレコードのみ抽出できていることが確認できました。

前方一致かつ後方一致

前方一致、後方一致の両方を条件とするパターンマッチングについて確認します。
これはここまで説明した前方一致と、後方一致の応用と捉えることができます。
それではサイズがXで始まり、Lで終わるというパターンマッチングを行ってみましょう。

SQLは次のように書きます。

SELECT * FROM orders 
WHERE サイズ LIKE 'X%L'

最初の1文字と末尾の1文字以外はどのような文字が何文字あっても構いません。
そのためワイルドカード'%'で、間の文字を置き換えています。

SQLを実行します。

注文日商品ID商品名サイズ
2020/1/6B_3000ショートパンツXL
2020/1/7B_3010ロングパンツXXL
2020/1/9B_3020ジーンズXXXL

サイズがXで始まり、Lで終わるレコードのみ抽出されたことが確認できました。

部分一致

今度はLIKEを使った部分一致のパターンマッチングについて確認します。
部分一致は「任意の場所に特定の文字列が含まれる」ことを意味します。

それでは商品idに'10'が含まれるレコードのみを抽出してみましょう。
SQLは次のように書きます。

SELECT * FROM orders 
WHERE 商品id LIKE '%10%'

'10'の前後にはどんな文字が何文字あってもよいので、'10'を挟むようにワイルドカードの'%'を配置します。

SQLを実行します。

注文日商品ID商品名サイズ
2020/1/1A_1000ダウンジャケットXS
2020/1/4A_1010TシャツS
2020/1/5A_2010カットソーL
2020/1/7B_3010ロングパンツXXL

商品idに'10'が含まれるレコードのみ抽出されたことが確認できました。

では「商品idの3文字目、4文字目が'10'である」というレコードのみ抽出したい場合は、どのようにすればよいでしょうか?
先ほどのSQLを次のように書きかえることで、意図したパターンマッチングを行うことができます。

SELECT * FROM orders 
WHERE 商品id LIKE '__10%'

'10'の前にはどのような文字があっても構いません。
ただし2文字である必要があります。
そのため任意の1文字を意味する'_'で1文字目、2文字目を置き換えます。
そして3文字目、4文字目に'10'を書きます。
5文字目以降は何が続いていも問題ないため、0文字以上の任意の文字列を意味する'%'で置き換えます。
このようにして検索するパターンを'__10%'と書き表しています。

それではSQLの実行結果を見てみましょう。

注文日商品ID商品名サイズ
2020/1/1A_1000ダウンジャケットXS
2020/1/4A_1010TシャツS

意図した通り商品idの3文字目、4文字目が'10'のレコードのみ抽出することができました。

NOT LIKEでパターンに一致しないレコードを抽出する

LIKE演算子の前に'NOT'を書くと、指定したパターンに一致しないレコードを抽出することができます。

先ほどの部分一致の説明で商品idに'10'を含むレコードの抽出を行いましたが、'NOT'を使って反対に'10'を含まないレコードの抽出を行ってみましょう。
SQLは次のように書くことができます。

SELECT * FROM orders 
WHERE 商品id NOT LIKE '%10%'

実行結果を確認します。

注文日商品ID商品名サイズ
2020/1/5A_2000ブルゾンM
2020/1/6B_3000ショートパンツXL
2020/1/9B_3020ジーンズXXXL

商品idに10が入らないレコードのみ抽出されたことが確認できました。

ワイルドカードのエスケープ処理

LIKE演算子を使ったパターンマッチングについて、理解は深まりましたでしょうか。
最後にワイルドカードのエスケープ処理について確認しておきましょう。

もし商品idに"_1"を含むレコードのみ抽出したい場合、どのようにLIKE演算子でパターンを指定すればよいでしょうか。
ここまでで学んだ知識を活かすと次のように書きたくなりますが、これでは意図したパターンマッチングを行うことができません。

SELECT * FROM orders 
WHERE 商品id LIKE '%_1%';

実行結果は次のようになり、「商品idの2文字目以降に'1'を含む」というパターンマッチングになってしまいます。

注文日商品ID商品名サイズ
2020/1/1A_1000ダウンジャケットXS
2020/1/4A_1010TシャツS
2020/1/5A_2010カットソーL
2020/1/7B_3010ロングパンツXXL

これはワイルドカードの'%'や''が特殊文字であるためです。
ワイルドカードの'%'や'
'のように、プログラム上で何らかの意味を持ち、そのままでは文字列として扱われない文字を特殊文字と呼びます。
特殊文字を文字列として扱うには、エスケープ処理が必要になります。
SQliteのエスケープ処理では、任意の文字をエスケープ文字に指定し、そのエスケープ文字を特殊文字の前に記載します。

エスケープ処理を用いた正しいSQLは次のようになります。

SELECT * FROM orders 
WHERE 商品id LIKE '%?_1%' ESCAPE '?';

エスケープ処理を行うときは、LIKE演算子と検索するパターンを書いた後、'ESCAPE'と書きます。
続いてエスケープ文字に指定する任意の文字を書きます。
このSQLではエスケープ文字として'?'を指定しています。
これにより'?'の直後の特殊文字は、文字列として扱われるようになります。
ではSQLを実行して、正しく'_1'のパターンマッチングを行うことができているか確認します。

注文日商品ID商品名サイズ
2020/1/1A_1000ダウンジャケットXS
2020/1/4A_1010TシャツS

意図したとおり商品idに'_1'を含むレコードのみを抽出できました。
このように検索対象の文字列に特殊文字が含まれる場合は、エスケープ処理をするのを忘れないようにしましょう。

レッスンで使ったファイルはこちら

■保存方法
Mac:右クリック⇒「リンク先を別名で保存」
Windows:右クリック⇒「名前を付けてリンク先を保存」

test_database.dbはこちら