LIKE は文字列をパターンと比べます。パターンの中の % は任意の長さの文字列に、_ はちょうど 1 文字に一致するので、email LIKE '%@gmail.com' で Gmail のアドレスが見つかります。LIKE は大文字と小文字を区別します。ILIKE は大文字と小文字を区別しない同じ照合で、~ と ~* は POSIX 正規表現で照合します。
Asha と Bella が返ります。ILIKE は Bella のアドレスの大文字を無視し、Chen の .co は末尾が一致しません。
% と _ で照合する
パターンは文字列全体に一致する必要があるので、% のないパターンは = と同じように働きます。NOT LIKE と NOT ILIKE は照合の結果を反転します。
_ はちょうど 1 文字を表すので、A-___ は A-100 に一致しますが、A-2000 には一致しません。小文字の a-300 は ILIKE の判定だけを通ります。
% や _ そのものを検索する
パターンの中のバックスラッシュは次の文字をエスケープするので、'%50\%%' で 50% を含む文字列が見つかります。データにバックスラッシュが含まれる場合は、ESCAPE で別のエスケープ文字を指定します。
SAVE\_% は SAVE_10 だけに一致します。エスケープしないと、_ は SAVE10 の 1 にも一致してしまいます。
~ を使う正規表現
正規表現の演算子は、~ (一致する)、~* (大文字小文字を区別せず一致する)、!~ と !~* (一致しない) です。正規表現は、^ と $ で固定しない限り、文字列のどこに一致してもかまいません。
ハイフン区切りの形式に一致するのは 555-0101 だけです。g フラグ付きの regexp_replace は数字以外をすべて取り除くので、(555) 0199 は 5550199 に、call me は空文字列になります。
一致した部分を取り出す、数える
ほかの正規表現関数は、真偽値ではなく一致した部分を返します。
| 関数 | 返すもの |
|---|---|
substring(s from 'pattern') | 最初の一致、または括弧で囲んだ最初のグループ |
regexp_match(s, 'pattern') | 最初の一致のグループを text[] で |
regexp_matches(s, 'pattern', 'g') | 一致ごとに 1 行 |
regexp_count(s, 'pattern') | 一致の数 (PostgreSQL 15 から) |
regexp_substr(s, 'pattern') | 最初の一致 (PostgreSQL 15 から) |
regexp_like(s, 'pattern') | ~ と同じ働きの関数 (PostgreSQL 15 から) |
regexp_split_to_table(s, 'pattern') | 一致の間の部分ごとに 1 行 |
substring('Order 4521 shipped' from '\d+') は 4521 を返します。フラグと正規表現の構文は、マニュアルの パターンマッチの章 に載っています。
パターン検索でインデックスを使う
LIKE 'abc%' のように先頭が固定されたパターンなら、データベースが C 照合順序を使っているか、インデックスを text_pattern_ops で作っている場合に B-tree インデックスが使えます。
CREATE INDEX users_email_prefix ON users (email text_pattern_ops);
% で始まるパターン、ILIKE、ほとんどの正規表現は B-tree を使えません。これらには、pg_trgm 拡張のトライグラムインデックスが LIKE、ILIKE、~、~* に使えます。
CREATE EXTENSION pg_trgm;
CREATE INDEX users_email_trgm ON users USING gin (email gin_trgm_ops);
パターンではなく大文字小文字を区別しない等価比較なら、lower(email) にインデックスを作り、lower(email) = 'ben@work.io' で検索するほうが簡単です。lower、position、split_part は 文字列関数のページ で扱います。
よくある質問
PostgreSQL で文字列に部分文字列が含まれるか調べるには?
col LIKE '%text%' を使います。大文字と小文字を区別しないなら col ILIKE '%text%' です。position('text' in col) > 0 や strpos(col, 'text') > 0 も使え、% や _ をエスケープする必要がありません。
PostgreSQL の LIKE は大文字と小文字を区別しますか?
区別します。'Apple' LIKE 'a%' は false です。ILIKE を使うか、lower(col) LIKE 'a%' で比べるか、大文字小文字を区別しない正規表現の演算子 ~* を使ってください。
複数のパターンを一度に照合するには?
LIKE ANY と配列を組み合わせます。name LIKE ANY (ARRAY['a%', 'b%']) はどれかのパターンに一致すれば真になり、LIKE ALL はすべてへの一致を求めます。name ~ '^(a|b)' のような正規表現の選択でも同じことができます。
PostgreSQL の SIMILAR TO とは何ですか?
LIKE と正規表現を混ぜた SQL 標準の構文です。% と _ に加えて |、*、+、グループを使い、文字列全体に一致する必要があります。たいていは LIKE か ~ 系の演算子のほうがわかりやすいです。