SQLのLIKEとは?%と_であいまい検索する書き方
LIKEは、文字列がパターンに一致するかを調べるWHEREの条件です。「ボールペンで始まる」「ペンを含む」のように、完全一致では書けないあいまいな検索ができます。%と_の2つの記号を、商品名と商品コードで確かめましょう。
例で使う表
productsは1行が商品1件です。nameは商品名、codeは商品コード、priceは価格(円)です。ペンはボールペン2種と蛍光ペン、ノートは2種、割引券は2種あります。蛍光ペンのコードだけ小文字のpen-ylで、割引券のコードは「SALE_10」と「SALE-10」で記号だけが違います。ORDER BYなしの結果の行順は一例で、保証されません。
| id | name | code | price |
|---|---|---|---|
| 1 | ボールペン黒 | PEN-BK | 120 |
| 2 | ボールペン赤 | PEN-RD | 120 |
| 3 | ノートA5 | NOTE-A5 | 300 |
| 4 | ノートB5 | NOTE-B5 | 350 |
| 5 | 蛍光ペン | pen-yl | 150 |
| 6 | 割引券(10%) | SALE_10 | 500 |
| 7 | 割引券(10円) | SALE-10 | 100 |
LIKEの基本構文
SELECT *
FROM products
WHERE name LIKE 'ボールペン%';LIKEと%で名前の先頭が同じ行を探す(前方一致)
LIKEは、列の値がパターンに一致する行を選ぶ条件です。パターンの%は、0文字以上の任意の文字列を表します。'ボールペン%'は「ボールペンで始まる名前」を意味するため、ボールペン黒とボールペン赤の2行が一致します。LIKEのパターンは文字列全体に一致する必要があります。%を付けずに'ボールペン'とだけ書くと、名前がちょうどボールペンの行だけが対象になり、この表では0行です。
SELECT *
FROM products
WHERE name LIKE 'ボールペン%';| id | name | code | price |
|---|---|---|---|
| 1 | ボールペン黒 | PEN-BK | 120 |
| 2 | ボールペン赤 | PEN-RD | 120 |
%で囲んで名前のどこかに含まれる行を探す(部分一致)
パターンの前後を%で囲むと、その文字列が名前のどこにあっても一致します。'%ペン%'はボールペン黒、ボールペン赤、蛍光ペンの3行を返します。蛍光ペンは「ペン」が末尾にあるため、後ろだけに%を付けた'ペン%'では見つかりません。先頭にだけ%を付けた'%A5'のようなパターンは、A5で終わる値を探す後方一致になります。
SELECT *
FROM products
WHERE name LIKE '%ペン%';| id | name | code | price |
|---|---|---|---|
| 1 | ボールペン黒 | PEN-BK | 120 |
| 2 | ボールペン赤 | PEN-RD | 120 |
| 5 | 蛍光ペン | pen-yl | 150 |
_で任意の1文字を表して文字数をそろえる
パターンの_は、任意の1文字ちょうどを表します。%が0文字以上なのに対し、_は文字数を固定できます。'NOTE-__'は「NOTE-」の後ろがちょうど2文字のコードなので、NOTE-A5とNOTE-B5が一致します。1文字だけの'NOTE-_'は、この表では0行です。
SELECT *
FROM products
WHERE code LIKE 'NOTE-__';| id | name | code | price |
|---|---|---|---|
| 3 | ノートA5 | NOTE-A5 | 300 |
| 4 | ノートB5 | NOTE-B5 | 350 |
NOT LIKEでパターンに一致しない行を選ぶ
NOT LIKEは、パターンに一致しない行を選びます。名前に「ペン」を含まない商品は、ノートA5、ノートB5と2種類の割引券の4行です。NOT LIKEもLIKEと同じくNULLとは比べられないため、対象の列がNULLの行は結果に出ません。この表のnameはNOT NULLなので該当しません。
SELECT *
FROM products
WHERE name NOT LIKE '%ペン%';| id | name | code | price |
|---|---|---|---|
| 3 | ノートA5 | NOTE-A5 | 300 |
| 4 | ノートB5 | NOTE-B5 | 350 |
| 6 | 割引券(10%) | SALE_10 | 500 |
| 7 | 割引券(10円) | SALE-10 | 100 |
ILIKEで大文字小文字を区別せずに探す
PostgreSQLのLIKEは、大文字小文字を区別します。code LIKE 'pen-%'だとpen-ylの1行だけで、PEN-BKやPEN-RDは一致しません。ILIKEにすると大文字小文字を区別せずに比べ、蛍光ペンとボールペン2種の3行が一致します。ILIKEはPostgreSQLの拡張で、標準SQLにはありません。
SELECT *
FROM products
WHERE code ILIKE 'pen-%';| id | name | code | price |
|---|---|---|---|
| 1 | ボールペン黒 | PEN-BK | 120 |
| 2 | ボールペン赤 | PEN-RD | 120 |
| 5 | 蛍光ペン | pen-yl | 150 |
%や_そのものを探すにはバックスラッシュでエスケープする
%や_を記号として探したいときは、直前にバックスラッシュ(\)を付けます。標準では\がエスケープ文字です。code LIKE 'SALE\_10'は「_」という文字を含むSALE_10だけに一致します。エスケープせずにSALE_10と書くと、_が任意の1文字になり、SALE-10も一致してしまいます。
SELECT *
FROM products
WHERE code LIKE 'SALE\_10';| id | name | code | price |
|---|---|---|---|
| 6 | 割引券(10%) | SALE_10 | 500 |
LIKEでよくある間違い
_を文字として探すつもりでエスケープしない
code LIKE 'SALE_10'は、SALE_10だけでなくSALE-10にも一致します。_は任意の1文字を表す記号だからです。記号そのものを探すときは\_と書きます。
SELECT *
FROM products
WHERE code LIKE 'SALE_10';%を付けずに部分一致のつもりで書く
name LIKE 'ペン'は完全一致と同じで、名前がちょうど「ペン」の行しか返しません。この表では0行です。含む行を探すなら'%ペン%'にします。
SELECT *
FROM products
WHERE name LIKE 'ペン';実際に1問解いてみる
LIKE・前方一致
商品名が「ボールペン」で始まる商品の全列を取得してください。行順は問いません。
関連構文
LIKEについてよくある質問
- LIKEと=はどう使い分けますか?
- =は値が完全に同じ行を探し、LIKEは%や_を含むパターンで探します。パターンに%も_も含まなければ、LIKEは=と同じ完全一致として働きます。完全一致なら=のほうが意図が明確です。
- 大文字小文字を区別せずに探すには?
- PostgreSQLのLIKEは区別するため、ILIKEを使います。この表ではcode ILIKE 'pen-%'が3行を返し、code LIKE 'pen-%'は1行です。
- NULLの値はLIKEで見つかりますか?
- 見つかりません。NULLにLIKEを適用した結果はNULLで、WHEREでは真にならないためです。NULLの行を探すにはIS NULLを使います。
- 日本語の名前でも_は使えますか?
- 使えます。_は1バイトではなく1文字を表します。name LIKE 'ノート__'は、ノートの後ろがちょうど2文字のノートA5とノートB5に一致します。
- LIKEより複雑な条件を書きたいときは?
- PostgreSQLにはLIKEのほかに、SIMILAR TOとPOSIX正規表現(~演算子など)があります。まずは%と_で足りるかを考え、複雑な規則が必要なときに公式のパターンマッチングの章を読むと整理できます。
参考文献
内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。
- PostgreSQL公式:LIKE — %と_の意味、ILIKE、エスケープ文字、ESCAPE句
- PostgreSQL公式:POSIX正規表現 — LIKEで足りない場合の正規表現によるパターンマッチ