SQL LIKE: 前方・後方・部分一致で絞り込む

SQL LIKE は、文字列列を SQL ワイルドカード付きのパターンと照合して行を絞り込みます。% は 0 文字以上、_ はちょうど 1 文字に一致します。SQL の前方一致フィルター（'prefix%'）、後方一致フィルター（'%suffix'）、部分一致フィルター（'%term%'）で使います。

前方一致の SQL LIKE の例

SQL エディター

CREATE TABLE customers (name TEXT, email TEXT);
INSERT INTO customers VALUES
  ('Alice', 'alice@corp.com'),
  ('Bob', 'bob@mail.org'),
  ('Alina', 'alina@corp.com'),
  ('Carol', 'carol@mail.org');
SELECT name, email FROM customers
WHERE name LIKE 'Al%'
ORDER BY name;

出力:

ここに出力が表示されます...

出力:

+-------+----------------+
| name  | email          |
+-------+----------------+
| Alice | [email protected] |
| Alina | [email protected] |
+-------+----------------+
2 row(s)

LIKE ‘Al%’ が行を絞り込む仕組み

CREATE TABLE と INSERT で customers に 4 行を投入します。
name LIKE 'Al%' は Al で始まる名前だけを残します。% は残りの文字列に一致します。
name に通常の B-tree インデックスがあれば、'Al%' のような前方一致パターンはインデックス範囲走査を使えます。

SQL LIKE 演算子とは？

SQL LIKE は文字列列をパターンと比較します。片側または両側に % を付けない限り、LIKE は部分文字列ではなく文字列全体に一致します。ワイルドカードなしの LIKE 'text' は = 'text' と同じ挙動です。列が NULL の場合、LIKE と NOT LIKE は unknown になり、WHERE でその行は除外されます。

ワイルドカードパターン: %, _, ESCAPE

% と _ で多くの絞り込み要件を満たせます。SQL WHERE のワイルドカードパターンでは、固定文字列に % と _ を組み合わせて前方一致、後方一致、部分一致を表現します。

パターン	意味	一致例
`'Al%'`	`Al` で始まる	Alice, Alina
`'%org'`	`org` で終わる	[email protected]
`'%mail%'`	`mail` を含む	[email protected]
`'_ob'`	任意の 1 文字 + `ob`	Bob

文字どおりの % または _ を検索するには、ESCAPE 句でエスケープ文字を定義します。

SELECT label FROM metrics
WHERE label LIKE '%100\%%' ESCAPE '\';

\ をエスケープ文字として宣言しているため、\% はワイルドカードではなく実際の % に一致します。

SQL NOT LIKE: 一致する行を除外する

NOT LIKE は一致条件を反転します。SQL の NOT LIKE クエリは、パターンに一致しないすべての行を返します。

SELECT name FROM customers
WHERE email NOT LIKE '%corp%';

このクエリは Bob と Carol を返します。つまり、メールアドレスに corp を含まない行です。列が NULL の行は LIKE と NOT LIKE のどちらでも除外されます。NULL も含めるには、OR column IS NULL を明示的に追加します。

SQL LIKE でよくある間違い

間違い: LIKE 'term' が部分一致になると思っている。

誤り:

WHERE name LIKE 'li'

正しい例:

WHERE name LIKE '%li%'

理由: LIKE は文字列全体に一致します。両側に % がないと、li と完全一致する場合だけ一致します。

間違い: ESCAPE を使わずに文字どおりの % を検索する。

誤り:

WHERE discount LIKE '100%'

正しい例:

WHERE discount LIKE '100\%' ESCAPE '\'

理由: ESCAPE がないと % はワイルドカードとして働き、100、1000、100USD、そして 100 で始まるその他の値にも一致します。

LIKE の性能: 前方一致と先頭ワイルドカード

通常の B-tree インデックスでは、LIKE 'abc%' はインデックス範囲走査を使えます。'%abc' や '%abc%' のように % で始まるパターンはインデックスシークを妨げるため、エンジンは一般により多くの行を走査します。

大規模な部分文字列検索では、LIKE '%term%' ではなく全文検索（FTS）を使います。

方言ごとの大文字小文字の扱い

LIKE の大文字小文字の扱いはデータベースエンジンごとに異なります。

データベース	LIKE の既定動作
PostgreSQL	大文字小文字を区別。区別しない比較は `ILIKE`
MySQL	列の照合順序に依存（多くは区別しない）
SQLite	既定では ASCII 文字で大文字小文字を区別しない

SQLite では、ASCII 範囲では LIKE 'al%' と LIKE 'Al%' は同じように扱われます。PRAGMA case_sensitive_like = ON を設定すると、厳密な大文字小文字一致に切り替わります。PostgreSQL の ILIKE は SQL 標準にはない拡張です。

列を LOWER() で包む方法は方言をまたいで使えますが、インデックスを使えなくなります。性能が重要なら、大文字小文字を区別しない照合順序か、方言固有の演算子を優先します。