こんにちは。キノコードです。
例えば、都道府県というカラムに、1つめのレコードに東京都、2つめのレコードに大阪府、3つめのレコードに京都府があったとします。
'府'で終わるレコードのみ抽出するにはどうしたらいいでしょうか?
また'京'が含まれるレコードを抽出すれにはどうしたらいいでしょうか?
そこで、この動画では、文字列の部分一致を条件としてデータ抽出する方法について説明します。
キノコードでは、この動画の他にも、たくさんのプログラミングのレッスンを配信しています。
チャンネル登録がまだの方は、チャンネルがどこにいったかわからなくならないように、チャンネル登録をお願いします。
プログラミング学習サービス「キノクエスト」のご紹介
キノコードでは、プログラミングを習得するためのプログラミング学習サービス「キノクエスト」を運営しています。
Pythonの問題の他に、SQLの問題も120問以上あります。
キノクエストには、学習カリキュラムがあり、学習順番に悩むことなく学習を進められます。
月額1,990円と本1冊分の値段です。
キノクエストの特徴は下記の通りです。
- SQLの問題も120問以上
- プログラミング学習をしている仲間が集まるコミュニティがある
- 1000問以上の問題を解いてプログラミングを習得
- 環境構築不要ですぐに始められる
- 動画と連動しているので、インプットもできる。
- 月額1,990円で、コミュニティもセット
キノクエストを詳しく知りたい方は、紹介ページをご覧ください。
▼キノクエストの紹介ページはこちら▼
https://kino-code.com/kq_service_a/
それでは、レッスンスタートです。
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/1 | A_1000 | ダウンジャケット | XS |
2021/1/4 | A_1010 | Tシャツ | S |
2021/1/5 | A_2000 | ブルゾン | M |
2021/1/5 | A_2010 | カットソー | L |
2021/1/6 | B_3000 | ショートパンツ | XL |
2021/1/7 | B_3010 | ロングパンツ | XXL |
2021/1/9 | B_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/1 | A_1000 | ダウンジャケット | XS |
2020/1/6 | B_3000 | ショートパンツ | XL |
2020/1/7 | B_3010 | ロングパンツ | XXL |
2020/1/9 | B_3020 | ジーンズ | XXXL |
サイズが'X'から始まるレコードのみ抽出できていることが確認できました。
もしサイズが「'X'から始まり、かつ2文字である」というパターンを検索するのであれば、使用するワイルドカードを変更します。
任意の1文字を示すワイルドカードは'_'でした。
SQLは次のように書きます。
SELECT * FROM orders
WHERE サイズ LIKE 'X_'
実行結果を確認します。
注文日 | 商品ID | 商品名 | サイズ |
---|---|---|---|
2020/1/1 | A_1000 | ダウンジャケット | XS |
2020/1/6 | B_3000 | ショートパンツ | XL |
実行結果からサイズが’XXL’、'XXXL'のレコードが外れました。
このように文字数を検索するパターンの条件に含めるのであれば、ワイルドカードは任意の1文字を表す'_'を使用するようにしましょう。
後方一致
次にLIKE演算子を使った後方一致のパターンマッチングを行います。
前方一致と考え方は同じなので、さくっと確認していきます。
それではサイズの末尾が'L'で終わるレコードのみを抽出してみましょう。
SQLは次のように書くことができます。
SELECT * FROM ordes
WHERE サイズ LIKE '%L'
今回検索するのはサイズの末尾が'L'であるパターンです。
それより前はどんな文字が何文字あっても構いません。
指定するパターンに関わらない部分はワイルドカードで置き換えます。
複数の文字列はワイルドカードの%を使って置き換えることができました。
このようにして、サイズの末尾が'L'で終わるというパターンを'%L'と書き表すことができました。
ではSQLを実行してみましょう。
注文日 | 商品ID | 商品名 | サイズ |
---|---|---|---|
2020/1/5 | A_2010 | カットソー | L |
2020/1/6 | B_3000 | ショートパンツ | XL |
2020/1/7 | B_3010 | ロングパンツ | XXL |
2020/1/9 | B_3020 | ジーンズ | XXXL |
サイズが'L'で終わるレコードのみ抽出できていることが確認できました。
前方一致かつ後方一致
前方一致、後方一致の両方を条件とするパターンマッチングについて確認します。
これはここまで説明した前方一致と、後方一致の応用と捉えることができます。
それではサイズがXで始まり、Lで終わるというパターンマッチングを行ってみましょう。
SQLは次のように書きます。
SELECT * FROM orders
WHERE サイズ LIKE 'X%L'
最初の1文字と末尾の1文字以外はどのような文字が何文字あっても構いません。
そのためワイルドカード'%'で、間の文字を置き換えています。
SQLを実行します。
注文日 | 商品ID | 商品名 | サイズ |
---|---|---|---|
2020/1/6 | B_3000 | ショートパンツ | XL |
2020/1/7 | B_3010 | ロングパンツ | XXL |
2020/1/9 | B_3020 | ジーンズ | XXXL |
サイズがXで始まり、Lで終わるレコードのみ抽出されたことが確認できました。
部分一致
今度はLIKEを使った部分一致のパターンマッチングについて確認します。
部分一致は「任意の場所に特定の文字列が含まれる」ことを意味します。
それでは商品idに'10'が含まれるレコードのみを抽出してみましょう。
SQLは次のように書きます。
SELECT * FROM orders
WHERE 商品id LIKE '%10%'
'10'の前後にはどんな文字が何文字あってもよいので、'10'を挟むようにワイルドカードの'%'を配置します。
SQLを実行します。
注文日 | 商品ID | 商品名 | サイズ |
---|---|---|---|
2020/1/1 | A_1000 | ダウンジャケット | XS |
2020/1/4 | A_1010 | Tシャツ | S |
2020/1/5 | A_2010 | カットソー | L |
2020/1/7 | B_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/1 | A_1000 | ダウンジャケット | XS |
2020/1/4 | A_1010 | Tシャツ | 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/5 | A_2000 | ブルゾン | M |
2020/1/6 | B_3000 | ショートパンツ | XL |
2020/1/9 | B_3020 | ジーンズ | XXXL |
商品idに10が入らないレコードのみ抽出されたことが確認できました。
ワイルドカードのエスケープ処理
LIKE演算子を使ったパターンマッチングについて、理解は深まりましたでしょうか。
最後にワイルドカードのエスケープ処理について確認しておきましょう。
もし商品idに"_1"を含むレコードのみ抽出したい場合、どのようにLIKE演算子でパターンを指定すればよいでしょうか。
ここまでで学んだ知識を活かすと次のように書きたくなりますが、これでは意図したパターンマッチングを行うことができません。
SELECT * FROM orders
WHERE 商品id LIKE '%_1%';
実行結果は次のようになり、「商品idの2文字目以降に'1'を含む」というパターンマッチングになってしまいます。
注文日 | 商品ID | 商品名 | サイズ |
---|---|---|---|
2020/1/1 | A_1000 | ダウンジャケット | XS |
2020/1/4 | A_1010 | Tシャツ | S |
2020/1/5 | A_2010 | カットソー | L |
2020/1/7 | B_3010 | ロングパンツ | XXL |
これはワイルドカードの'%'や''が特殊文字であるためです。
ワイルドカードの'%'や''のように、プログラム上で何らかの意味を持ち、そのままでは文字列として扱われない文字を特殊文字と呼びます。
特殊文字を文字列として扱うには、エスケープ処理が必要になります。
SQliteのエスケープ処理では、任意の文字をエスケープ文字に指定し、そのエスケープ文字を特殊文字の前に記載します。
エスケープ処理を用いた正しいSQLは次のようになります。
SELECT * FROM orders
WHERE 商品id LIKE '%?_1%' ESCAPE '?';
エスケープ処理を行うときは、LIKE演算子と検索するパターンを書いた後、'ESCAPE'と書きます。
続いてエスケープ文字に指定する任意の文字を書きます。
このSQLではエスケープ文字として'?'を指定しています。
これにより'?'の直後の特殊文字は、文字列として扱われるようになります。
ではSQLを実行して、正しく'_1'のパターンマッチングを行うことができているか確認します。
注文日 | 商品ID | 商品名 | サイズ |
---|---|---|---|
2020/1/1 | A_1000 | ダウンジャケット | XS |
2020/1/4 | A_1010 | Tシャツ | S |
意図したとおり商品idに'_1'を含むレコードのみを抽出できました。
このように検索対象の文字列に特殊文字が含まれる場合は、エスケープ処理をするのを忘れないようにしましょう。
以上でレッスンは終わりです。
キノコードでは、プログラミングを習得するためのプログラミング学習サービス「キノクエスト」を運営しています。
Pythonの問題の他に、SQLの問題も120問以上あります。
キノクエストには、学習カリキュラムがあり、学習順番に悩むことなく学習を進められます。
月額1,990円と本1冊分の値段です。
キノクエストの特徴は下記の通りです。
- SQLの問題も120問以上
- プログラミング学習をしている仲間が集まるコミュニティがある
- 1000問以上の問題を解いてプログラミングを習得
- 環境構築不要ですぐに始められる
- 動画と連動しているので、インプットもできる。
- 月額1,990円で、コミュニティもセット
キノクエストを詳しく知りたい方は、紹介ページをご覧ください。
▼キノクエストの紹介ページはこちら▼
https://kino-code.com/kq_service_a/