データベースにおける正規表現【REGEXP】：AABlog：So-netブログ

	ブログをはじめるログイン

Proxmoxクラスタでノードを落とした.. ｜電源スイッチを外付けブログトップ

データベースにおける正規表現【REGEXP】　[SQLポケリ] [編集]

さて、本日はようやくデータベースにおける正規表現をみていこうと思う。
まずは、Oracle23cでやってみる。

DSC_1228[1].jpg

Oracle23cはProxmoxのVMにOracle Linux 8を入れてその中にOracle23cをインストールしている。

REGEXP_LIKE

最初に紹介するのはREGEXP_LIKEである。
これはSQL標準のLIKEによるあいまい検索を「正規表現」でやる感じの関数である。
以下の例を見て貰えればわかりやすいであろう。

select a from foo where regexp_like(a,'^a.*');

a
------
abc
agc
abcabc

regexp_likeの第一引数には元となる文字列を入れる。上記の例の場合は、fooテーブルのa列を指定している。ちなみにfooテーブルのa列には以下のようなデータが格納されている。

select a from foo;

a
----------
abc
123
agc
8139too
0120-PMC
abcabc

このデータの中から正規表現「^a.*」にマッチするものだけを検索して表示させるのがregexp_likeを使った先の例文である。

regexp_likeの第一引数には元となる文字列を入れ、続く第二引数には正規表現で示した文字列を与える。
regexp_likeの機能は、第一引数で与えられた文字列の中に第二引数の正規表現で示された文字列が存在していれば真を返し、存在しなければ偽を返す、といったものになる。

第二引数の正規表現の部分にメタ文字を含んでいなくてもかまわない。これがLIKEとちょっと違う点なのでハマりポイントかも知れない。

例えばLIKEで「文字1を含むもの」といった検索をしようとしたら、以下のようにすると思う。

select a from foo where a like '%1%';

a
----------
123
8139too
0120-PMC

これをregexp_likeでやろうとしたら、以下のようにできる。

select a from foo where regexp_like(a, '1');

a
----------
123
8139too
0120-PMC

もちろんメタ文字を含んでもOK

select a from foo where regexp_like(a, '.*1.*');

a
----------
123
8139too
0120-PMC

LIKEでは「何々を含む」といった検索を行う場合、何々の前後にメタ文字%を付け、何々の前後が任意の文字列であっても検索に引っかかるようにしないといけない。
REGEXP_LIKEではメタ文字を使うことなく、単に「何々」を指定すればOKで「何々」を含むかどうかでヒットするのである。

前方一致と後方一致

あいまい検索の場合、その検索のやり方にいくつかパターンがあり、前方一致、後方一致などがある。「何々を含む」といった検索は部分一致になる。

前方一致は、「何々から始まる」という検索方法で、LIKEでやるなら以下のように後ろに%を付けると前方一致になる。

select a from foo where a like '1%';

a
----------
123

1から始まるものを検索。

正規表現の場合、「1.*」としても先頭の文字が1でない場合もヒットしてしまうので、先頭を意味するメタ文字である^を使って「^1.*」としなければならない。
^を付けずに実行すると...

select a from foo where regexp_like(a,'1.*');

a
----------
123
8139too
0120-PMC

先頭が1でないものまでヒットする。

select a from foo where regexp_like(a,'^1.*');

a
----------
123

先頭を意味するメタ文字^を加えて「^1.*」とすればLIKE '1%'と同じになる。

後方一致は逆に「何々で終わる」ものを検索する。LIKEでは「%1」とすれば良い。REGEXP_LIKEでは終わりを意味するメタ文字$を使って「.*1$」にすればOK。

合わせ技で「aで始まりcで終わる」という検索も可能。
LIKEなら「a%c」
REGEXP_LIKEなら「^a.*c$」
とすればOK。

何れかにマッチさせる

あいまい検索を行う際に「何々」または「うんたら」の両方を検索したいっていう場合もあるでしょう。SQL命令のOR条件を使うような場合である。
例のfooテーブルから「123」か「abc」を含むものを検索する時は次のようなSQL命令になるであろう。

select a from foo where a like '%123%' or a like '%abc%';

a
----------
abc
123
abcabc

LIKEのメタ文字には「または」を意味するようなものが存在しない。正規表現にはグループ化という概念があり、グループのうち何れかとマッチすればOKといった検索が可能。そのため1回のREGEXP_LIKE呼び出しで上記の「123」か「abc」を含むものを検索するクエリを記述できる。

select a from foo where regexp_like(a, '(123|abc)');

a
----------
abc
123
abcabc

ほらね。できたでしょ。
グループは(で始まり、)で終わる。グループの中はマッチさせたい文字列を列挙するが区切りを|で表記する。3つ以上でもかまわない。
グループ内の文字列にメタ文字を含んでいても良い。

select a from foo where regexp_like(a, '(123|a.*c)');

a
----------
abc
123
agc
abcabc

123かa.*cにマッチするものを検索。

このグループ化の機能は「大した機能じゃない」と思われるかも知れないが、置換や抽出とかやり出すと重宝する。置換についてはREGEXP_REPLACE関数で解説しようと思う。

REGEXP_LIKEは、Oracleの他、DB2、MySQLでも使用できる。PostgreSQLでは~演算子で正規表現によるパターンマッチングが可能。

REGEXP_COUNT

次に紹介する正規表現関数は、REGEXP_COUNTである。正規表現関数は頭がREGEXPとなっている。
REGEXP_COUNTは元の文字列内に正規表現とマッチする部分文字列が、いくつあるのかを数えて戻す関数。REGEXP_LIKEがマッチする/しないを真偽で返すことに対して、REGEXP_COUNTはマッチしないのであれば0、マッチするのであればその回数を戻すため、1以上の数値となる。

select a from foo where regexp_count(a, '(123|abc)') >= 1;

a
----------
abc
123
abcabc

WHERE条件で使う場合は、REGEXP_LIKEの方がいいかもね。
正規表現のパターンが複数回マッチするような割と大きめのデータでマッチした回数を調べたいときには便利かもね。

select a, regexp_count(a, 'abc') from foo where regexp_like(a, 'abc');

a          regexp_count
---------- ------------
abc                   1
abcabc                2

abcabcはabcが2回マッチしていることがわかる。
「指定したキーワードが10回以上出現する文書を検索したい」とかに使えるかも。処理速度はあまり期待できないかもだけど。

ロンゲストマッチ

ここで、正規表現を「a.*c」に変えてマッチする様子を見てみようと思う。先ほどはメタ文字なしのabcにマッチするデータを検索したのだが、a.*cにするとどうなるかやってみようということである。多分マッチするデータが増えるはず。

select a, regexp_count(a, 'a.*c') from foo where regexp_like(a, 'a.*c');

a          regexp_count
---------- ------------
abc                   1
agc                   1
abcabc                1

予想通り、abc、abcabcに加えagcもマッチするようになったわけだが、マッチする回数がなんかおかしいような。

abcとagcはそれそのものしかないので、1回の出現回数となる。しかし、abcabcはa.*cが2回出現しているのでは？現にabcを指定した前の例では出現回数が2となっている。

　なんかバグってる？

いえそんなことはないのである。
正規表現ではロンゲストマッチというマッチング(検索)処理が行われる。ロンゲストはlongの最大級(long longer longest)で一番長いを意味する。

abcabcには、a.*cにマッチする部分文字列が一見するとふたつあるように思えるが、abcの部分にもマッチするし、abcabcの全体でもマッチする。どちらもaで始まりcで終わっているからね。
先ほど述べたように正規表現はロンゲストマッチでマッチング処理を行うので、より長い方の結果が優先されることになる。

なので、regexp_countの結果も1になるわけである。

実は私も良く知らなかったのだが、ロンゲストマッチではなくショーテストマッチに変更できるとのこと。ロンゲストマッチはよく聞くのだが、ショーテストマッチ(最短一致)なんてあまり聞かんぞ。
メタ文字*は、0回以上の繰り返しを意味する。この繰り返しを意味するメタ文字には*の他+や{n}といったものもある。これらの繰り返しを意味するメタ文字に?を付けると最短一致になるというのである。

ここで整理のため繰り返しを意味するメタ文字をまとめておく。

量指定子	最短一致	メタ文字の意味
*	*?	0回以上の繰り返し
+	+?	1回以上の繰り返し
?	??	あるかないかのどちらか
{N}		N回の繰り返し
{min,}	{min,}?	min回以上のの繰り返し
{,max}	{,max}?	max回以下の繰り返し
{min,max}	{min,max}?	min回以上でmax回以下の繰り返し

では実際にやってみよう。前例の正規表現を「a.*?c」にしてみる。

select a, regexp_count(a, 'a.*?c') from foo where regexp_like(a, 'a.*?c');

a          regexp_count
---------- ------------
abc                   1
agc                   1
abcabc                2

確かにマッチングの様子が変わった。
正確には最短ではなく、最短最左になるみたいだが、ネストしているような特殊な場合だけ期待した通りにならないようなので、?を付ければ最短一致と覚えておけば良いであろう。

REGEXP_COUNTは、Oracleの他、DB2でも使用できる。PostgreSQL、MySQLでは使用できない。

REGEXP_INSTR

次に紹介する3番目の正規表現関数は、REGEXP_INSTRである。
RDBMSにはREGEXP無しの正規表現に対応していない、INSTR関数が古くから存在している。INSTR関数は文字列の中から文字列を検索する関数である。例えば以下のように使用する。

select a, instr(a, 'bc') from foo where regexp_like(a, 'a.*c');

a          instr
---------- -----
abc            2
agc            0
abcabc         2

INSTRは部分文字列を検索して最初に見つかった部分の桁数を戻す。「abc」の中から「bc」を検索すると、2文字目に見つかるので2が結果として得られている。
正規表現でa.*cと条件を付けているので、「agc」もマッチして行が返されるが、INSTRでは正規表現が使用できないので、bcが見つからず結果は0となっている。

REGEXP_INSTRはINSTRと同様に文字列中から文字列を検索するが、正規表現を与えることが可能。さっそく上記のINSTRの例をREGEXP_INSTRに変えてみよう。

select a, regexp_instr(a, 'a.*c') from foo where regexp_like(a, 'a.*c');

a          regexp_instr
---------- ------------
abc                   1
agc                   1
abcabc                1

REGEXP_INSTRに変更するとともに、正規表現パターンも条件式と同じ「a.*c」に変えてみた。agcについてもちゃんとヒットしていることがわかる。

REGEXP_INSTR関数では最初に見つけた部分文字列の位置を文字数で戻すことに注意しなければならない。JavaScriptにおけるmatchメソッドのように複数の結果が戻されることはない。ヒットする部分文字列が複数回出現するような場合は、regexp_instrの第4引数で出現回数を都度指定すれば良い。全体の出現回数はregexp_countで取得可能。

regexp_instr関数での引数は最大で7個まで指定が可能。最初の4つまではinstrとほとんど同じで、第2引数の検索する文字列で正規表現が使えるかどうかの違い。第5引数からregexp_instrで拡張された部分になる。
何番目に出現するものなのかの指定は第4引数なので、instr関数でも指定可能である。

まぁやってみればなんとなくわかるでしょう。

select a, regexp_instr(a, 'a.*?c') instr, regexp_instr(a, 'a.*?c', 1, 2) instr2
 from foo where regexp_like(a, 'a.*?c');

a             instr    instr2
---------- -------- ---------
abc               1         0
agc               1         0
abcabc            1         4

最短マッチにしないとabcabcが2回マッチしないので、a.*?cに変更している。select句で3つのフィールドを用意。最初がa列の内容、次が1回目の検索結果、最後が2回目の検索結果。abcabcでは2回目の検索結果が4文字目であることがわかる。

検索して見つからない場合は0が戻ってくる。マッチする箇所を全部処理したい時に、regexp_countで個数を取得しておいてその分ループをまわす、でも良いし、regexp_instrの第4引数にカウンタとなるループ変数を与えて戻りが0になるまで繰り返す、という感じでも実装できる。

REGEXP_INSTRは、Oracleの他、DB2、MySQLでも使用できる。PostgreSQLでは使用できない。

REGEXP_REPLACE

さて、次に紹介するのはREGEXP_REPLACE関数である。こちらもINSTRと同じように、正規表現に対応していないREPLACE関数が存在する。REGEXP_REPLACE関数、REPLACE関数はどちらも文字列置換を行う関数である。REGEXP_REPLACEとREPLACEの違いは正規表現を使って検索ができるかどうかである。

REPLACE関数の引数は全部で最低3つ指定する必要がある。
対象となる元の文字列、置換前の検索を行う文字列、置換後の文字列の3つである。
正規表現対応のREGEXP_REPLACE関数には第2引数(置換前の検索を行う文字列)と第3引数(置換後の文字列)に正規表現を使用することができる。

置換後の文字列に正規表現を使うといってもどう使えば良いのかピンと来ないかも知れない。まぁ読み進めてもらえれば理解できると思うので、まずは簡単な例から見てみよう。

select a, regexp_replace(a, '^a', 'A') replace from foo;

a          replace   
---------- ----------
abc        Abc
123        123
agc        Agc
8139too    8139too
0120-PMC   PMC 0120
abcabc     Abcabc

先頭のaを大文字のAに文字列置換してみた。正規表現に対応していないREPLACE関数だと、abcabcがAbcAbcに置換されることになる。

次に、置換後の文字列にメタ文字を使った例を見てみよう。

select a, regexp_replace(a, '(.*)\-(.*)', '\2 \1') rrep from foo;

a          rrep   
---------- ----------
abc        abc
123        123
agc        agc
8139too    8139too
0120-PMC   PMC 0120
abcabc     abcabc

正規表現がムズくなってしまった。なんか括弧ばかり付いてしまったし、謎の記号だらけである。申し訳ない。

ここで注目すべきは\-である。-は正規表現のメタ文字の一種なので、\を付けることでエスケープして-そのものを意味するようにしてある。データを見ると「0120-PMC」といったデータに-が含まれているので、\-でここにマッチすると考えてOK(\が￥と表示される環境もあるので注意)。

置換後の結果を見ると-が含まれる「0120-PMC」のデータだけ置換されていることがわかる。

\-の左右に任意の文字列を意味する.*が付いており、それが()で囲まれている感じになっている。この括弧はグループ化の指定でメタ文字の1種であるため実際のデータに(や)が含まれる必要はない。「0120-PMC」のデータでも括弧は含んでいない。

ここでの正規表現はバックスラッシュで-がエスケープされていること。()によるグループがふたつ存在することが難しく感じられる要因であろう。

さて、置換後の文字列に注目してみよう。第3引数には置換後の文字列を指定する。指定された文字列に置き換わるわけなのだが、ここに正規表現のメタ文字を使うことができる。メタ文字と言っても、*や.ではなく、\1や\2という感じのバックスラッシュに続く数字で構成されるメタ文字となる(MySQLでは\ではなく$を使用)。

バックスラッシュに続く数字が意味するのは、グループの番号である。ここでの正規表現にはグループがふたつ存在している。それらのグループを順番に\1、\2と表すことができるのである。


　(.*)\-(.*)
   ↑　　↑
　　\1　　\2

.*は任意の文字列を意味するので、マッチング処理の結果どんな文字列にもなり得る。abcでもagcでも123でもよいわけである。
間に-があるので、この部分は譲れない。マッチングしないと置換対象にならない。例にしているデータだと「0120-PMC」しかヒットしないことになる。

0120-PMCだけを例にしてどのようにマッチングされるかを見てみると...


　　０１２０－ＰＭＣ
       ↑   ↑  ↑
　（．＊）￥－（．＊）
     ↑          ↑
     \1　　      \2

みたいな感じになる。
-を挟んで、左側の0120が\1になり、右側のPMCが\2に対応することになる。置換後の文字列指定で\2 \1と順番を逆にしているので、「PMC 0120」と置換されるのである。

REGEXP_REPLACEは、Oracleの他、DB2、PostgreSQL、MySQLでも使用できる。

REGEXP_SUBSTR

さて、最後に紹介するのはREGEXP_SUBSTR関数である。こちらも同じように、正規表現に対応していないSUBSTR関数が存在する。INSTRが文字列検索してその位置を戻すのに対してSUBSTRは部分文字列を返す。
その違いだけなので、さらっと例を上げるだけにしておく。

select a, regexp_substr(a, 'a.*?c') substr,
  regexp_substr(a, 'a.*?c', 1, 2) substr2 from foo where regexp_like(a, 'a.*?c');

a          substr   substr2
---------- -------- ---------
abc        abc      
agc        agc      
abcabc     abc      abc

REGEXP_SUBSTRは、Oracleの他、DB2でも使用できる。