PostgreSQL正規表現の使い方｜regexp_replaceと~演算子

PostgreSQLの正規表現とは、~ 演算子で「マッチするか」を判定し、regexp_replace などの関数で「置換・抽出・分割・カウント」を行う仕組みです。 LIKE より細かい条件を1つの式で書けるので、データの検査や整形にとても役立ちます。

この記事では、LIKE・SIMILAR TO・~ 系演算子を同じデータで比べた結果表と、PostgreSQL 15 で加わった regexp_count・regexp_instr・regexp_like・regexp_substr までを含む関数早見表を用意しました。載せている SQL と出力は、すべて PostgreSQL 16.15 で実際に実行した結果です。gフラグの有無、regexp_matches で行数が減る挙動、エスケープのつまずきも再現しています。

PostgreSQL正規表現の早見表

演算子（マッチするかを true / false で返す）

関数（置換・抽出・分割・カウント）

引数の並びは PostgreSQL 16 の文書（関数とパターンマッチのページ）で確認しました。「使える版」の列は一般に知られた仕様に基づく記載で、今回実行して確かめたのは PostgreSQL 16.15 です。

よく使うフラグ

LIKE・SIMILAR TO・チルダ演算子の違いを同じデータで比べる

まず検証用のテーブルを作ります。以降の SQL はそのまま貼って動かせます。

CREATE TABLE items (id int, code text);
INSERT INTO items VALUES
 (1,'abc'),(2,'ABC'),(3,'abc123'),(4,'xabcx'),(5,'a.c'),(6,'a%c'),(7,NULL);

SELECT id, code,
       code LIKE 'abc'                   AS "like_abc",
       code LIKE 'abc%'                  AS "like_abc%",
       code ILIKE 'abc'                  AS "ilike_abc",
       code SIMILAR TO 'abc'             AS "similar_abc",
       code SIMILAR TO '(abc|ABC)%'      AS "similar_alt",
       code ~   'abc'                    AS "re",
       code ~   '^abc$'                  AS "re_anchor",
       code ~*  'abc'                    AS "re_i",
       code !~  'abc'                    AS "not_re",
       code !~* 'abc'                    AS "not_re_i"
FROM items ORDER BY id;

実行結果（PostgreSQL 16.15。\pset null (null) で NULL を表示）:

 id |  code  | like_abc | like_abc% | ilike_abc | similar_abc | similar_alt |   re   | re_anchor |  re_i  | not_re | not_re_i 
----+--------+----------+-----------+-----------+-------------+-------------+--------+-----------+--------+--------+----------
  1 | abc    | t        | t         | t         | t           | t           | t      | t         | t      | f      | f
  2 | ABC    | f        | f         | t         | f           | t           | f      | f         | t      | t      | f
  3 | abc123 | f        | t         | f         | f           | t           | t      | f         | t      | f      | f
  4 | xabcx  | f        | f         | f         | f           | f           | t      | f         | t      | f      | f
  5 | a.c    | f        | f         | f         | f           | f           | f      | f         | f      | t      | t
  6 | a%c    | f        | f         | f         | f           | f           | f      | f         | f      | t      | t
  7 | (null) | (null)   | (null)    | (null)    | (null)      | (null)      | (null) | (null)    | (null) | (null) | (null)
(7 rows)

この表から読み取れるポイントは4つです。

• LIKE と SIMILAR TO は文字列全体に対して判定し、~ は部分一致で判定します。 xabcx（id=4）にマッチするのは ~ 'abc' だけです。~ で全体一致にしたいときは ^abc$ のように先頭と末尾を固定します。
• ~* と ILIKE は大文字小文字を区別しません。 ABC（id=2）で結果が分かれています。
• SIMILAR TO は % _ に加えて、選択の縦棒・かっこ・繰り返しが使えます。 (abc|ABC)% は「abc か ABC で始まる」という意味で、id=1〜3 にマッチしました。
• NULL に対する結果は、どの演算子でも NULL です。 !~ でも true にはなりません。「マッチしない行」を集めるときに NULL の行も含めたい場合は、code IS NULL OR code !~ '...' と書きます。

ドットとアンダースコアの扱いの違い

「1文字」を表す記号は方式ごとに違います。

SELECT id, code,
       code LIKE 'a.c'        AS l,
       code SIMILAR TO 'a.c'  AS s,
       code ~ 'a.c'           AS r,
       code ~ 'a\.c'          AS r_esc,
       code LIKE 'a_c'        AS l_us,
       code SIMILAR TO 'a_c'  AS s_us
FROM items WHERE id IN (1,5,6) ORDER BY id;

 id | code | l | s | r | r_esc | l_us | s_us 
----+------+---+---+---+-------+------+------
  1 | abc  | f | f | t | f     | t    | t
  5 | a.c  | t | t | t | t     | t    | t
  6 | a%c  | f | f | t | f     | t    | t
(3 rows)

どれを選ぶかの判断表

regexp_replaceで置換する（gフラグの有無で結果が変わる）

regexp_replace は、フラグを付けないと最初の1か所だけを置換します。 すべて置換するには第4引数に 'g' を付けます。

SELECT regexp_replace('2026-10-03','-','/')     AS no_g,
       regexp_replace('2026-10-03','-','/','g') AS with_g;

    no_g    |   with_g   
------------+------------
 2026/10-03 | 2026/10/03
(1 row)

replace() 関数は常に全部を置換するので、その感覚で regexp_replace を使うと「1か所しか変わらない」という結果になります。「全部置換したいなら g」と覚えておくと迷いません。

大文字小文字を区別しないで置換する

フラグは並べて書けます。'gi' は「すべて」かつ「大文字小文字を区別しない」です。

SELECT regexp_replace('Tokyo tokyo TOKYO','tokyo','X','gi') AS gi,
       regexp_replace('Tokyo tokyo TOKYO','tokyo','X','g')  AS g;

  gi   |       g       
-------+---------------
 X X X | Tokyo X TOKYO
(1 row)

かっこで囲んだ部分を置換後に使う

パターンのかっこで囲んだ部分は、置換後の文字列で \1 \2 として使えます。マッチ全体は \& です。

SELECT regexp_replace('yamada taro','(\w+) (\w+)','\2 \1') AS swap,
       regexp_replace('price 1200','\d+','[\&]')           AS amp;

    swap     |     amp      
-------------+--------------
 taro yamada | price [1200]
(1 row)

N番目だけ置換する・途中から置換する

開始位置と N を指定する書き方です（15以降の引数）。N に 0 を指定すると、g フラグと同じく全部を置換します。

SELECT regexp_replace('a-b-c-d','-','/',1,2) AS second_only,
       regexp_replace('a-b-c-d','-','/',1,0) AS n0_all,
       regexp_replace('a-b-c-d','-','/',3)   AS from3;

 second_only | n0_all  |  from3  
-------------+---------+---------
 a-b/c-d     | a/b/c/d | a-b/c-d
(1 row)

from3 は「3文字目以降で最初に見つかった1か所」を置換した結果です。

最長一致と最短一致

.* はできるだけ長くマッチします。短く止めたいときは .*? と書きます。

SELECT regexp_replace('<b>x</b><b>y</b>','<b>.*</b>','[]')      AS greedy,
       regexp_replace('<b>x</b><b>y</b>','<b>.*?</b>','[]','g') AS lazy;

 greedy | lazy 
--------+------
 []     | [][]
(1 row)

regexp_matchとregexp_matchesで取り出す

regexp_matchは最初のマッチを配列で返す

SELECT regexp_match('order-2026-0042','(\d{4})-(\d+)')      AS m,
       (regexp_match('order-2026-0042','(\d{4})-(\d+)'))[2] AS m2,
       regexp_match('order','\d+')                          AS nomatch,
       regexp_match('abc123','\d+')                         AS nogroup;

      m      |  m2  | nomatch | nogroup 
-------------+------+---------+---------
 {2026,0042} | 0042 | (null)  | {123}
(1 row)

• かっこがあれば、かっこごとの文字列が配列の要素になります。
• かっこが無ければ、マッチ全体が要素1つの配列で返ります。
• 要素を取り出すときは、関数呼び出し全体をかっこで囲んでから [1] を付けます。 囲まずに書くと次のエラーになります。

ERROR:  syntax error at or near "["
LINE 1: select regexp_matches('abc','b')[1];
                                        ^

regexp_matchesはマッチごとに1行を返す

SELECT regexp_matches('a1b22c333','\d+');        -- g なし
SELECT regexp_matches('a1b22c333','\d+','g');    -- g あり
SELECT regexp_matches('k1=v1;k2=v2','(\w+)=(\w+)','g');

 regexp_matches 
----------------
 {1}
(1 row)

 regexp_matches 
----------------
 {1}
 {22}
 {333}
(3 rows)

 regexp_matches 
----------------
 {k1,v1}
 {k2,v2}
(2 rows)

g なしでは最初のマッチの1行だけ、g ありではマッチの数だけ行が返ります。

regexp_matchesで行数が減る挙動を再現する

regexp_matches は「集合を返す関数」です。マッチしなかった行は、NULL になるのではなく、結果から行ごと無くなります。

CREATE TABLE memo (id int, body text);
INSERT INTO memo VALUES (1,'tel 03-1234-5678'),(2,'no number'),(3,'a 11 b 22');

SELECT id, regexp_matches(body,'\d+') FROM memo;

 id | regexp_matches 
----+----------------
  1 | {03}
  3 | {11}
(2 rows)

3行のテーブルなのに、数字を含まない id=2 が結果に出てきません。g フラグを付けると、今度は行が増えます。

SELECT id, regexp_matches(body,'\d+','g') FROM memo;

 id | regexp_matches 
----+----------------
  1 | {03}
  1 | {1234}
  1 | {5678}
  3 | {11}
  3 | {22}
(5 rows)

件数を保ったまま最初のマッチを取りたいときは、regexp_match（末尾に es が付かないほう） を使います。

SELECT id, (regexp_match(body,'\d+'))[1] AS first_num FROM memo;

 id | first_num 
----+-----------
  1 | 03
  2 | (null)
  3 | 11
(3 rows)

すべてのマッチを取りつつ、マッチが無い行も残したいときは LEFT JOIN LATERAL を使います。

SELECT m.id, t.x
FROM memo m
LEFT JOIN LATERAL regexp_matches(m.body,'\d+','g') AS t(x) ON true
ORDER BY 1;

 id |   x    
----+--------
  1 | {03}
  1 | {1234}
  1 | {5678}
  2 | (null)
  3 | {11}
  3 | {22}
(6 rows)

また、regexp_matches は WHERE 句には書けません。判定には ~ 演算子を使います。

SELECT id FROM memo WHERE regexp_matches(body,'\d+') IS NOT NULL;

ERROR:  set-returning functions are not allowed in WHERE

SELECT id FROM memo WHERE body ~ '\d+';   -- id = 1, 3 の2行が返る

分割・カウント・位置・抽出の関数

区切り文字で配列や行に分割する

区切り文字が混ざっていても、文字クラスでまとめて指定できます。

SELECT regexp_split_to_array('a, b;c   d','[,;\s]+') AS arr;
SELECT regexp_split_to_table('a, b;c   d','[,;\s]+') AS part;
SELECT regexp_split_to_array('abc','')               AS chars;

    arr    
-----------
 {a,b,c,d}
(1 row)

 part 
------
 a
 b
 c
 d
(4 rows)

  chars  
---------
 {a,b,c}
(1 row)

空文字のパターンを渡すと1文字ずつに分かれます。

PostgreSQL 15以降のregexp_count・regexp_instr・regexp_like・regexp_substr

SELECT regexp_count('a1b22c333','\d+')    AS cnt,
       regexp_count('ABCabc','b',1,'i')   AS cnt_i;

SELECT regexp_instr('a1b22c333','\d+')       AS pos1,
       regexp_instr('a1b22c333','\d+',1,2)   AS pos2,
       regexp_instr('a1b22c333','\d+',1,2,1) AS end2,
       regexp_instr('abc','\d')              AS none;

SELECT regexp_like('Hello','^h')     AS l,
       regexp_like('Hello','^h','i') AS li;

SELECT regexp_substr('a1b22c333','\d+')                   AS s1,
       regexp_substr('a1b22c333','\d+',1,3)               AS s3,
       regexp_substr('key=value','(\w+)=(\w+)',1,1,'',2)  AS sub2,
       regexp_substr('abc','\d+')                         AS none;

 cnt | cnt_i 
-----+-------
   3 |     2
(1 row)

 pos1 | pos2 | end2 | none 
------+------+------+------
    2 |    4 |    6 |    0
(1 row)

 l | li 
---+----
 f | t
(1 row)

 s1 | s3  | sub2  |  none  
----+-----+-------+--------
 1  | 333 | value | (null)
(1 row)

• regexp_count はマッチの数を返します。「g を付けた regexp_matches を数える」よりも短く書けます。
• regexp_instr は N 番目のマッチの開始位置（1始まり）を返します。第5引数に 1 を渡すと、マッチの直後の位置が返ります。マッチしないときは 0 です。
• regexp_like は ~ 演算子の関数版です。フラグ 'i' を付けると ~* と同じ判定になります。
• regexp_substr は「N 番目のマッチ」や「かっこの何番目か」を文字列でそのまま返すので、配列の添字が要りません。

PostgreSQL 14 以前では、従来からある substring(文字列 from パターン) が使えます。かっこがあればかっこの中、無ければマッチ全体を返します。

SELECT substring('a1b22c333' from '\d+')   AS sub,
       substring('key=value' from '=(\w+)') AS subg;

 sub | subg  
-----+-------
 1   | value
(1 row)

エスケープのつまずきを実行結果で確認する（E文字列とバックスラッシュ）

通常の文字列ではバックスラッシュは1つでよい

現在の PostgreSQL は standard_conforming_strings が既定で on です（今回の環境でも on）。この設定では、通常の '...' の中のバックスラッシュはそのまま1文字として正規表現に渡ります。

SHOW standard_conforming_strings;                 -- on
SELECT 'a.b.c' ~ '\.'                        AS a,
       regexp_replace('a.b.c','\.','-','g')  AS b;

 a |   b   
---+-------
 t | a-b-c
(1 row)

E文字列ではバックスラッシュを2つ書く

E'...' は、文字列の中でバックスラッシュを「エスケープ記号」として解釈する書き方です。そのため、正規表現にバックスラッシュを渡すには2つ重ねます。

SELECT E'\d' AS e_d, '\d' AS plain_d, E'\\d' AS e_dd;

 e_d | plain_d | e_dd 
-----+---------+------
 d   | \d      | \d
(1 row)

E'\d' は、ただの d になっています。この違いがそのまま判定結果に出ます。

SELECT 'abc123' ~ E'\d+'  AS e1,
       'abc123' ~ E'\\d+' AS e2,
       'abc123' ~ '\d+'   AS p;

 e1 | e2 | p 
----+----+---
 f  | t  | t
(1 row)

置換では、もっと目立つ結果になります。E'\.' は .（任意の1文字）になるため、すべての文字が置き換わります。

SELECT regexp_replace('a.b.c', E'\.',  '-', 'g') AS e_single;
SELECT regexp_replace('a.b.c', E'\\.', '-', 'g') AS e_double;

 e_single 
----------
 -----
(1 row)

 e_double 
----------
 a-b-c
(1 row)

アプリケーションのコードから SQL を組み立てる場合は、プログラミング言語側の文字列でもバックスラッシュの解釈があります。「言語の文字列 → SQL の文字列 → 正規表現」の順に1段ずつ確認すると、どこで消えたかが見つけやすくなります。プレースホルダでパターンを渡せば、SQL の文字列の段を考えずに済みます。

記号そのものにマッチさせたいとき

+ ( [ . \ などは正規表現で意味を持つ記号です。文字として使うときはバックスラッシュを前に置きます。

SELECT regexp_replace('a+b','+','-');

ERROR:  invalid regular expression: quantifier operand invalid

SELECT regexp_replace('a+b','\+','-')           AS plus_ok,   -- a-b
       regexp_replace('C:\dir\file','\\','/','g') AS bs;      -- C:/dir/file

今回確認したエラー文を一覧にします。

数値や日付の列に正規表現を使うときは、col::text と型を変換してから渡します。

LIKEのエスケープは別のルール

LIKE では % と _ が特別な記号で、文字として使うときは \% \_ と書きます。正規表現では % はただの文字です。

SELECT regexp_replace('100%','%',' percent') AS pct,       -- 100 percent
       '100%' LIKE '100\%'                   AS like_esc,  -- t
       'a_c'  LIKE 'a\_c'                    AS us_esc;    -- t

利用者が入力した文字列を LIKE の部分一致に使うときは、正規表現で % _ \ を先にエスケープしておくと安心です。

SELECT '%' || regexp_replace('50%_off','([%_\\])','\\\1','g') || '%' AS like_escaped;
-- 結果: %50\%\_off%

目的別コピペ集（郵便番号・電話番号・メール・全角半角・数字抽出）

すべて PostgreSQL 16.15 で実行して結果を確かめたものです。

郵便番号を判定・整形する

SELECT '123-4567'  ~ '^\d{3}-\d{4}$'  AS zip_ok,    -- t
       '1234567'   ~ '^\d{3}-?\d{4}$' AS zip_opt,   -- t（ハイフンなしも許可）
       '123-45678' ~ '^\d{3}-\d{4}$'  AS zip_ng;    -- f

SELECT regexp_replace('1234567','^(\d{3})(\d{4})$','\1-\2') AS zip_fmt;  -- 123-4567

電話番号を判定する・数字だけにそろえる

SELECT t,
       t ~ '^0\d{1,4}-\d{1,4}-\d{4}$'                   AS tel_hyphen,
       regexp_replace(t,'\D','','g')                    AS digits,
       regexp_replace(t,'\D','','g') ~ '^0\d{9,10}$'    AS tel_digits
FROM (VALUES ('03-1234-5678'),('090-1234-5678'),('0312345678'),('12-34-5678')) v(t);

       t       | tel_hyphen |   digits    | tel_digits 
---------------+------------+-------------+------------
 03-1234-5678  | t          | 0312345678  | t
 090-1234-5678 | t          | 09012345678 | t
 0312345678    | f          | 0312345678  | t
 12-34-5678    | f          | 12345678    | f
(4 rows)

先に \D（数字以外）を取り除いてから桁数で判定すると、ハイフンの有無に左右されません。

メールアドレスの形式を大まかに確認する

SELECT m, m ~* '^[a-z0-9._%+-]+@[a-z0-9-]+(\.[a-z0-9-]+)+$' AS ok
FROM (VALUES ('taro@example.com'),('Taro.Yamada+tag@mail.example.co.jp'),
             ('taro@example'),('taro example.com')) v(m);

                 m                  | ok 
------------------------------------+----
 taro@example.com                   | t
 Taro.Yamada+tag@mail.example.co.jp | t
 taro@example                       | f
 taro example.com                   | f
(4 rows)

これは「形式がおおよそ正しいか」を見る簡易チェックです。実在するアドレスかどうかは、確認メールを送る方法で確かめます。

数字だけを取り出す・数値にする

SELECT regexp_replace('合計 1,280 円（税込）','\D','','g')          AS digits,  -- 1280
       (regexp_replace('合計 1,280 円','\D','','g'))::int + 1       AS num;     -- 1281

SELECT regexp_substr('room 12, floor 3','\d+') AS first_n,                              -- 12
       ARRAY(SELECT (regexp_matches('room 12, floor 3','\d+','g'))[1]) AS all_n;        -- {12,3}

全角と半角をそろえる・判定する

全角英数字を半角にするには、正規表現よりも translate や normalize が向いています。

SELECT translate('ＡＢＣ１２３－４５','ＡＢＣ０１２３４５６７８９－','ABC0123456789-') AS half,
       normalize('ＡＢＣ１２３ｶﾀｶﾅ', NFKC) AS nfkc;

   half    |      nfkc      
-----------+----------------
 ABC123-45 | ABC123カタカナ
(1 row)

normalize(..., NFKC) は全角英数字を半角に、半角カタカナを全角にまとめてそろえます（データベースの文字コードが UTF8 のときに使えます）。判定には文字クラスを使います。

SELECT s,
       s ~ '^[0-9]+$'        AS half_digit,
       s ~ '^\d+$'           AS d,
       s ~ '^[０-９]+$'      AS full_digit,
       s ~ '[^\x01-\x7E]'    AS has_non_ascii
FROM (VALUES ('123'),('１２３'),('12a')) v(s);

   s    | half_digit | d | full_digit | has_non_ascii 
--------+------------+---+------------+---------------
 123    | t          | t | f          | f
 １２３ | f          | f | t          | t
 12a    | f          | f | f          | f
(3 rows)

今回の環境（UTF8、ロケール C.UTF-8）では、\d は全角数字にマッチしませんでした。ロケールの設定で変わる可能性があるため、半角数字だけに限定したいときは [0-9] と書くと確実です。

ひらがな・カタカナだけかを判定する

SELECT s, s ~ '^[ぁ-ん]+$' AS hira, s ~ '^[ァ-ヶー]+$' AS kata
FROM (VALUES ('ひらがな'),('カタカナ'),('漢字')) v(s);

    s     | hira | kata 
----------+------+------
 ひらがな | t    | f
 カタカナ | f    | t
 漢字     | f    | f
(3 rows)

その他の整形パターン

フラグと複数行テキストの扱い

既定では、^ と $ は文字列全体の先頭と末尾だけにマッチします。行ごとに判定したいときは n フラグを付けます。

SELECT E'a\nb' ~ '^b$'                              AS def,  -- f
       regexp_like(E'a\nb','^b$','n')               AS n,    -- t
       regexp_replace('a  b','a \s+ b # comment','X','x') AS x,   -- X
       'ab' ~ '(?i)AB'                              AS embedded;  -- t

• x フラグを使うと、パターンの中に空白やコメントを入れて読みやすく書けます。
• (?i) のようにパターンの先頭にフラグを埋め込む書き方も使えます。~ 演算子でフラグを付けたいときに便利です。

正規表現とインデックス（検索が遅いと感じたとき）

20万行のテーブルで実行計画を確認しました。

CREATE TABLE big AS
SELECT g AS id, 'user'||g||'@example.com' AS email FROM generate_series(1,200000) g;
CREATE INDEX big_email_idx ON big(email text_pattern_ops);
ANALYZE big;

EXPLAIN (COSTS OFF) SELECT * FROM big WHERE email ~ '^user123@';

                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Index Scan using big_email_idx on big
   Index Cond: ((email ~>=~ 'user123@'::text) AND (email ~<~ 'user123A'::text))
   Filter: (email ~ '^user123@'::text)
(3 rows)

^ で先頭を固定した、大文字小文字を区別するパターンは、B-tree インデックスの範囲検索に置き換えてもらえます。 今回はロケールが C.UTF-8 のデータベースで、text_pattern_ops を指定したインデックスで確認しました（指定なしの B-tree との比較は未実行です）。先頭を固定できない検索を速くしたい場合は、pg_trgm 拡張の GIN インデックスが候補になります（この記事では未実行です）。

よくある質問

Q. ~ と LIKE はどちらを使えばよいですか？ A. 前方一致や部分一致だけなら LIKE、文字の種類や桁数まで指定したいなら ~ が向いています。~ は部分一致なので、全体を判定するときは ^ と $ を付けます。

Q. regexp_replace で1か所しか置換されません。 A. 第4引数に 'g' を付けてください。フラグなしは最初の1か所だけが対象です。regexp_replace('2026-10-03','-','/','g') で 2026/10/03 になります。

Q. regexp_matches を使ったら結果の行数が減りました。 A. マッチしない行は結果から無くなる仕様です。行数を保ちたいときは regexp_match（NULL が返る）か、LEFT JOIN LATERAL を使います。

Q. \d が効きません。 A. E'\d' と書いていないか確認してください。E文字列ではバックスラッシュが解釈されて d だけが残ります。通常の '\d' か、E'\\d' と書くと意図どおりに動きます。

まとめ

• LIKE と SIMILAR TO は全体一致、~ は部分一致。~ で全体を判定するなら ^...$ を付ける。
• ~* は大文字小文字を区別しない、!~ と !~* は「マッチしない」。NULL に対する結果はすべて NULL。
• regexp_replace はフラグなしだと最初の1か所だけ。全部なら 'g'、大文字小文字を区別しないなら 'gi'。
• regexp_matches はマッチしない行が無くなり、g を付けると行が増える。件数を保つなら regexp_match。
• PostgreSQL 15 以降は regexp_count・regexp_instr・regexp_like・regexp_substr が使える。配列の添字なしで数える・取り出すができる。
• 通常の文字列ではバックスラッシュ1つ、E文字列では2つ。E'\d' は d になる。
• 先頭を ^ で固定したパターンはインデックスを使える。固定しない検索と ~* は全行を読む。

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。