こんにちは。キノコードです。
例えば、都道府県というカラムに、1つめのレコードに東京都、2つめのレコードに大阪府、3つめのレコードに京都府があったとします。
'府'で終わるレコードのみ抽出するにはどうしたらいいでしょうか？
また'京'が含まれるレコードを抽出すれにはどうしたらいいでしょうか？
そこで、この動画では、文字列の部分一致を条件としてデータ抽出する方法について説明します。
キノコードでは、この動画の他にも、たくさんのプログラミングのレッスンを配信しています。
チャンネル登録がまだの方は、チャンネルがどこにいったかわからなくならないように、チャンネル登録をお願いします。
この記事の信頼性
この記事は、Youtubeにて日本最大級のプログラミング教育のチャンネルを運営しているキノコードが執筆、監修しています。
私自身は、2012年からプログラミング学習を始め、2019年以降はプログラミング教育に携わってきた専門家です。
他にも、私には下記のような実績や専門性があります。
- キノコードは毎月10名以上、合計100名以上ののプログラミング学習者と1対1でお悩みを聞き、アドバイスをしています
- キノコード自身は、プログラミングスクールに通ったり、本や有料åの動画で勉強してきた経験もあります
- キノコードは、プログラミング学習サービス「キノクエスト」を運営しています
- 本の出版、プログラミング雑誌への寄稿の実績があります
プログラミング学習サービス「キノクエスト」のご紹介
キノコードでは、プログラミングを習得するためのプログラミング学習サービス「キノクエスト」を運営しています。
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文字と末尾の１文字以外はどのような文字が何文字あっても構いません。
そのためワイルドカード'%'で、間の文字を置き換えています。
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/
