SQL練習室
← SQLガイド

PostgreSQLの正規表現とは?~・~*・regexp_replace・substringの使い方

LIKEの%と_では書けない「0で始まる2〜4桁の市外局番」「数字以外を取り除く」といった柔軟なパターンは、正規表現で書きます。PostgreSQLでは~演算子で一致を調べ、regexp_replaceで置き換え、substringで一部を取り出せます。顧客の電話番号とメールアドレスで、基本から使い分けまでを確認します。正規表現演算子(~、~*、!~)はPostgreSQLの拡張で、標準SQLのLIKE・SIMILAR TOとは別の仕組みです。

例で使う表

customersは顧客1件が1行で、emailはメールアドレス、phoneは電話番号です。メールアドレスの大文字小文字が混在し、西村工業の電話番号は「不明」という文字列、東西商会の電話番号は未入力(NULL)です。ORDER BYのないSQLの行順は保証されません。LIKEの%・_の使い方は、LIKEのガイドで先に確認できます。

customers:顧客6件
idnameemailphone
1青木商店aoki@Example.com03-1234-5678
2北野物産kitano@example.co.jp090-1111-2222
3中央商事info@example.com0120-333-444
4大和食品yamato@EXAMPLE.COM06-9876-5432
5西村工業nishimura@sample.org不明
6東西商会tozai@example.comNULL

正規表現の基本構文

sql
SELECT id, name, phone
FROM customers
WHERE phone ~ '^0[36]-';

~で正規表現に一致する行を選ぶ

phone ~ '^0[36]-'は、phoneが「先頭が0で、次が3か6、そのあとハイフン」というパターンに一致するかを調べます。^は文字列の先頭、[36]は3または6のどちらか1文字です。青木商店(03-...)と大和食品(06-...)が残ります。LIKEはパターンが文字列全体に一致する必要がありますが、正規表現は文字列のどこにでも一致できるので、先頭や末尾に限定したいときは^や$を自分で書きます。NULLの電話番号は一致の結果もNULLなので、東西商会は残りません。

sql
SELECT id, name, phone
FROM customers
WHERE phone ~ '^0[36]-'
ORDER BY id;
実行結果
idnamephone
1青木商店03-1234-5678
4大和食品06-9876-5432

~*は大文字小文字を区別しない

~は大文字小文字を区別し、~*は区別しません。メールアドレスのドメインがexample.comで終わるかを両方で調べると、~では小文字のexample.comの中央商事と東西商会だけがtrueです。~*ではExample.comの青木商店やEXAMPLE.COMの大和食品もtrueになります。example.co.jpは.comで終わらないので、どちらもfalseです。ドット(.)は任意の1文字を表すため、本物のドットは\.と書きます。

sql
SELECT
  id,
  email ~ 'example\.com$' AS case_sensitive,
  email ~* 'example\.com$' AS ignore_case
FROM customers
ORDER BY id;
実行結果
idcase_sensitiveignore_case
1falsetrue
2falsefalse
3truetrue
4falsetrue
5falsefalse
6truetrue

!~は一致しない行を選ぶ

!~は、パターンに一致しない行を選びます。phone !~ '^[0-9-]+$'は、数字とハイフンだけでできていない電話番号なので、「不明」と入った西村工業が残ります。ほかの行は数字とハイフンだけなので除かれます。NULLの電話番号は!~の結果もNULLになり、東西商会は選ばれません。未入力も含めたいときは、phone IS NULLを別にORでつなぎます。

sql
SELECT id, name, phone
FROM customers
WHERE phone !~ '^[0-9-]+$'
ORDER BY id;
実行結果
idnamephone
5西村工業不明

LIKEでは書けない形式チェックは正規表現を使う

「0で始まり、数字が1〜3桁、ハイフン、3〜4桁、ハイフン、4桁」のような桁数と文字種の指定は、LIKEでは書けません。正規表現なら[0-9]{3,4}のように繰り返し回数を指定できます。青木商店、北野物産、大和食品は形式に合いますが、中央商事の0120-333-444は最後が3桁なのでfalseです。単純な前方一致や部分一致ならLIKEのほうが短く読みやすいので、パターンが%と_で足りるかを先に考えます。

sql
SELECT
  id,
  phone,
  phone ~ '^0[0-9]{1,3}-[0-9]{3,4}-[0-9]{4}$' AS valid_format
FROM customers
ORDER BY id;
実行結果
idphonevalid_format
103-1234-5678true
2090-1111-2222true
30120-333-444false
406-9876-5432true
5不明false
6NULLNULL

regexp_replaceで一致した部分を置き換える(gフラグ)

regexp_replace(phone, '[^0-9]', '', 'g')は、数字以外([^0-9])の文字を空文字に置き換え、数字だけにします。第4引数のgは、一致した部分をすべて置き換えるフラグです。gがないと最初の1か所だけが置き換わり、03-1234-5678なら最初のハイフンだけが消えて031234-5678になります。「不明」は数字を含まないので空文字になり、NULLの電話番号は置換してもNULLです。

sql
SELECT id, regexp_replace(phone, '[^0-9]', '', 'g') AS digits
FROM customers
ORDER BY id;
実行結果
iddigits
10312345678
209011112222
30120333444
40698765432
5''(空文字)
6NULL

substring(文字列 from パターン)で一部を取り出す

substring(email from '@(.+)$')は、パターンに一致した部分を取り出します。カッコ()がある場合は、カッコの中に一致した部分だけが返り、ここでは@より後ろのドメインです。aoki@Example.comならExample.comが取り出せます。パターンに一致しない行はNULLになります。複数のカッコの部分をまとめて取り出したいときは、regexp_matchが配列で返します。

sql
SELECT id, substring(email from '@(.+)$') AS domain
FROM customers
ORDER BY id;
実行結果
iddomain
1Example.com
2example.co.jp
3example.com
4EXAMPLE.COM
5sample.org
6example.com

正規表現でよくある間違い

正規表現の中でLIKEの%を使う

正規表現では%は特別な意味を持たない普通の文字です。phone ~ '03%'は「03のあとに%がある」という意味になり、03-1234-5678には%がないので1行も一致しません。「03で始まる」は^03と書き、任意の文字列は.*で表します。

sql
SELECT id, phone
FROM customers
WHERE phone ~ '03%';

実際に1問解いてみる

~

電話番号phoneが03または06で始まり、そのあとにハイフンが続く顧客の全列を取得してください。行順は問いません。

テーブル

読み込み中…

実行結果

関連構文

正規表現についてよくある質問

LIKEと正規表現はどう使い分けますか?
前方一致・後方一致・部分一致のように%と_で書けるならLIKEを使います。桁数、文字種、複数の候補(03または06)の指定など、LIKEで書けない条件に正規表現を使います。LIKEは文字列全体に一致する必要があり、正規表現は一部に一致すれば成功する点も違います。
~と~*のどちらを使えばよいですか?
大文字小文字を区別したいときは~、区別しないときは~*です。メールアドレスのドメインのように、大文字小文字が混在していても同じものとして扱いたい場合は~*を使います。
正規表現で一致しない行を選ぶにはどうしますか?
!~(大文字小文字を区別しない場合は!~*)を使います。NOT (列 ~ パターン)と書いても同じです。どちらも列がNULLの行はNULLになり、選ばれない点に注意してください。
regexp_replaceで全部を置き換えるにはどうしますか?
第4引数のフラグにgを指定します。gがないと、最初に一致した1か所だけが置き換わります。大文字小文字を区別しないiフラグと組み合わせて、'gi'のようにも書けます。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。