×××より後ろの文字列抽出
ある文字列（河沼郡湯川村）より後ろの文字列を抽出します。
select unnest(regexp_matches('河沼郡湯川村大字勝常村','(?<=河沼郡湯川村).*')) matchtext
;
--×××より後ろの文字列抽出
--
myposdb=# select unnest(regexp_matches('河沼郡湯川村大字勝常村','(?<=河沼郡湯川村).*')) matchtext
myposdb-# ;
matchtext
------------
大字勝常村
(1 row)
myposdb=#×××より前の文字列抽出
ある文字列（大字勝常村）より前の文字列を抽出します。
select unnest(regexp_matches('河沼郡湯川村大字勝常村','.*(?=大字勝常村)')) matchtext
;
--×××より前の文字列抽出
--
myposdb=# select unnest(regexp_matches('河沼郡湯川村大字勝常村','.*(?=大字勝常村)')) matchtext
myposdb-# ;
matchtext
--------------
河沼郡湯川村
(1 row)
myposdb=#囲まれた部分の抽出
(カッコ)に囲まれた部分の抽出
select unnest(regexp_matches( '山田(10点)川田(20点)森田(15点)' ,'\(.+?\)','g')) as unsetext ;
-- (カッコ)に囲まれた部分の抽出
myposdb=# select unnest(regexp_matches(
myposdb(# '山田(10点)川田(20点)森田(15点)'
myposdb(# ,'\(.+?\)'
myposdb(# ,'g'
myposdb(# )) as unsetext;
unsetext
----------
(10点)
(20点)
(15点)
(3 rows)カッコに囲まれた中身の抽出
Oracleでは肯定先読み、肯定戻り読みがサポートされていないらしく今回のような「囲まれた中身だけの抽出」というのが（一気には）できませんが、PostgreSQLではきちんとサポートされています。
select unnest(regexp_matches( '山田(10点)川田(20点)森田(15点)' ,'(?<=\().*?(?=\))' ,'g' )) as unsetext ;
-- カッコに囲まれた中身の抽出
myposdb=# select unnest(regexp_matches(
myposdb(# '山田(10点)川田(20点)森田(15点)'
myposdb(# ,'(?<=\().*?(?=\))'
myposdb(# ,'g'
myposdb(# )) as unsetext;
unsetext
----------
10点
20点
15点
(3 rows)
最短一致での抽出
-- ポイントは.*?です。.*と書いてしまうと「河沼郡湯川村大字勝常村」までがマッチしてしまいます。
myposdb=# select unnest(regexp_matches('河沼郡湯川村大字勝常村','.*?郡*村')) matchtext;
matchtext
--------------
河沼郡湯川村
-- ××郡××町のケースも想定した場合上記では都合が悪いです。
-- しかもこの例題は「郡村」と続く最悪のケースです。
-- 狙うは「柴田郡村田町」です
myposdb=# select unnest(regexp_matches('柴田郡村田町大字村田７','.*?郡*村')) matchtext;
matchtext
-----------
柴田郡村 ←思惑と違う
-- 一時間近くも格闘した事恥ずかしいですが、、なんとか完成したのが以下です。
myposdb=# select unnest(regexp_matches('柴田郡村田町大字村田７','.+?(?:郡|郡村).+[村|町]')) matchtext;
matchtext
--------------
柴田郡村田町
-- 先の××郡××村もこの通り
myposdb=# select unnest(regexp_matches('河沼郡湯川村大字勝常村東','.+?(?:郡|郡村).+[村|町]')) matchtext;
matchtext
--------------
河沼郡湯川村
(1 row)補足
-- 補足
-- 当初 '.*?郡*村|.*?郡*町' と記載すればよいと思っていましたがこれが思うようにいきませんでした。
--
myposdb=# select unnest(regexp_matches('柴田郡村田町大字村田７','.*?郡*村|.*?郡*町')) matchtext;
matchtext
--------------------
柴田郡村田町大字村
(1 row)
-- いろいろ調べた結果 OR（|パイプ）で区切った場合、「常に最長マッチ」となるようです。
-- https://www.postgresql.jp/document/13/html/functions-matching.html
-- ”|演算子で接続された2つ以上のブランチからなるREは常に欲張り型です。”
-- 正規表現はいろんな環境で利用されますが、それぞれ微妙に独自の制限が入っていたり
-- 他と違ったりするので、これもその一つなのでしょう、、
-- せめて最短マッチ・最長マッチの何れかが選べるようにしてもらうといいのになぁ、、
１丁目は含めたいけど１１丁目は外したい
案外遭遇するシーンですが、正規表現を使わずこの条件で抽出するのは大変です。
-- １丁目は条件に含めたいけど１１丁目は含めたくない with sample01 as ( select '新宿１丁目' addr union select '新宿３丁目' addr union select '新宿１１丁目' addr union select '新宿１２丁目' addr ) select * from sample01 where addr ~ '^(?=.*(１丁目|１２丁目))(?!.*１１丁目).*$' ;
-- １丁目は条件に含めたいけど１１丁目は含めたくない
-- (?=xxx) を含むもの、でも(?!XXX) は含めないを表現しています。
-- 今回は、１丁目と１２丁目を抽出したいけれど、１１丁目が含まれてしまうので
-- 否定先読み(?!xxx)を使って除外しています。ちなみに(?=xxx)は肯定先読みと呼ばれています。
--
myposdb=# with sample01 as (
myposdb(# select '新宿１丁目' addr union
myposdb(# select '新宿３丁目' addr union
myposdb(# select '新宿１１丁目' addr union
myposdb(# select '新宿１２丁目' addr
myposdb(# )
myposdb-# select * from sample01 where addr ~ '^(?=.*(１丁目|１２丁目))(?!.*１１丁目).*$'
myposdb-# ;
addr
--------------
新宿１丁目
新宿１２丁目
(2 rows)
myposdb=#住所を扱う際に活躍する正規表現
都道府県始まりの住所を抽出
addressカラムの先頭から、２文字もしくは３文字続いた後[都道府県]の何れかで終わる文字列が存在するのか判断します。??は直前の文字が０個もしくは１個の場合の最短マッチなので２文字もしくは３文字となります。
address ~ '^...??[都道府県]'
-- テストデータ
myposdb=# select * from addrestest order by 1;
dtno | address
------+--------------------------------------
1 | 北海道上川郡清水町本通八丁目７
2 | 上川郡清水町本通八丁目７
3 | 出身は北海道上川郡清水町本通八丁目７
(3 rows)
-- 検索結果
myposdb=# select dtno,address from addrestest
myposdb-# where address ~ '^...??[都道府県]'
myposdb-# order by dtno;
dtno | address
------+--------------------------------
1 | 北海道上川郡清水町本通八丁目７
(1 row)
myposdb=#住所分割
これは100点回答になっていません。（今後に期待！）
まず、(グループ1)(グループ2)(グループ3)(グループ4)$ としているのは分析対象文字列を漏れなく４分割するために行っています。
前提として住所に使われる英数字は全て全角である事
select unnest( regexp_matches( '京都府京都市上京区寺町通今出川上る３丁目西入上る上片原町３４' ,'(...??[都道府県])(.+?郡[^町]+?[町村]|.+?市.+?区|.+?市|.+?区|[^区]?町|.+?村)([^町村]+[町村]|[^０-９Ａ-Ｚ]+|[０-９Ａ-Ｚ]+)(.*)$' ) )as annest ;
myposdb=# select unnest(
myposdb(# regexp_matches(
myposdb(# --'三重県四日市市市場町１０'
myposdb(# '京都府京都市上京区寺町通今出川上る３丁目西入上る上片原町３４'
myposdb(# --'京都府綴喜郡井手町大字多賀東松ケ花１４'
myposdb(# --'千葉県市原市能満２０８９'
myposdb(# --'愛知県蒲郡市西浦町稲村５'
myposdb(# --'兵庫県多可郡多可町中区中安田１０'
myposdb(# --'宮城県仙台市青葉区ＳＳ３０住友生命仙台中央ビル１階'
myposdb(# --'北海道空知郡上富良野町西６線北１３２'
myposdb(# ,'(...??[都道府県])(.+?郡[^町]+?[町村]|.+?市.+?区|.+?市|.+?区|[^区]?町|.+?村)([^町村]+[町村]|[^０-９Ａ-Ｚ]+|[０-９Ａ-Ｚ]+)(.*)$'
myposdb(# ) )as annest;
annest
----------------------------
京都府
京都市上京区
寺町通今出川上る
３丁目西入上る上片原町３４
(4 rows)
myposdb=#
