DB2 9では、pureXML(XML-DB)機能やPL/SQL等、さまざまな機能強化が行われどんどん便利になっていますが、残念ながら正規表現が使用できる関数（以下、正規表現関数）は組み込み関数として提供されていません。もともとSQLにはあいまい検索機能としてLIKEによる一致検索がありますが、"%"(任意の0文字以上の文字列)と"_"(任意の1文字)を使用した一致検索のみであり、正規表現の機能性と比べると見劣りする部分が有りました。

正規表現は、文字列を検索したり置換したりする際、大変便利な指示表現であり、さまざまな環境や言語でサポートされています。（正規表現自体の基本的な説明は、割愛させて頂きます。）

DB2の良きライバルであるOracle Databaseでは10gから、正規表現関数が組み込み関数として用意されています。これは私の勝手な考えですが、DB2にとってパフォーマンス上の観点からあえて提供していないのかもしれません。そのような問題が有るとしても、正規表現を使い慣れている人にとってはSQL上でも使用したい場面があるはずです。

ここまで読んで『DB2でもなにか有ったような･･･』と思われた方は正解です。2003年に『SQLに正規表現マッチングのパワーを活用』（*1）という記事が公開されDB2 UDB v7の頃から正規表現関数をUDF（以下、正規表現UDF）として利用できました。ただ公開されたのは、外部の正規表現エンジンをC言語UDFで利用するもので、導入にはコンパイル環境や正規表現エンジン導入に伴うDB2の外部環境の変更が必要で、気軽に導入できるものではありませんでした。(誤解の無いように…記事自体は明解です。)

この記事では、DB2 9で新たにサポートされたXQueryにある正規表現関数を利用して、外部ライブラリに寄らない正規表現UDFとして簡単に導入する方法を紹介します。

上に戻る

正規表現UDFの概要

この記事では8つの正規表現UDFを紹介します。XQueryに存在する3つの関数(fn:matches(),fn:replace(),fn:tokenize)だけでなく便利と思われる関数も作成しているので用途に合わせて使用して下さい。

表1．提供する正規表現UDF一覧

UDF名	種類	機能
REG_MATCHES	スカラー	一致有無確認
REG_REPLACE	スカラー	文字列置換
REG_COUNT	スカラー	一致回数取得
REG_POSITION	スカラー	一致位置取得
REG_SUBSTR	スカラー	一致文字列取得
REG_SUBSTR_TABLE	表	一致文字列情報一覧
REG_TOKENIZE_TABLE	表	不一致文字列情報一覧（区切り文字列による分割）
REG_ALLTOKEN_TABLE	表	一致・不一致文字列情報一覧

上に戻る

各UDFの機能詳細

REG_MATCHES関数

機能
検索対象文字列に指定された正規表現パターンに一致する部分が存在するかを戻すスカラー関数です。
一致する部分が存在する場合 1 を、存在しない場合 0 を戻します。
正規表現パターンには、副次式の参照として\1～\9が使用できます。
XQueryのfn:matches関数(*3)と同一構文です。
OracleのREGEXP_LIKE条件(*7)に相当する機能として使用可能です。

構文

>>-REG_MATCHES(source-string,pattern-+--------+-)---------------><
                                     '-,flags-'

source-string:検索対象文字列
pattern:正規表現パターン
flags:正規表現オプション(省略可)

使用例

WITH DATA(TEXT) AS (VALUES 'ABC','GHI','XYZ','',NULL)
SELECT TEXT,REG_MATCHES(TEXT,'A.+|X.+') FROM DATA;

TEXT  2 
----- --
ABC    1
GHI    0
XYZ    1
       0
-      0

REG_REPLACE関数

機能
検索対象文字列の指定された正規表現パターンに一致する部分を置換文字列に置き換えた結果を戻すスカラー関数です。
一致する部分が存在しない場合、検索対象の文字列をそのまま戻します。
正規表現パターンには、副次式の参照として\1～\9が使用できます。
置換文字列には、一致する部分全体の参照として$0が、副次式の参照として$1～$9が使用できます。
XQueryのfn:replace関数(*4)と同一構文です。
OracleのREGEXP_REPLACE関数(*8)に相当する機能として使用可能です。

構文

>>-REG_REPLACE(source-string,pattern,replacement-string-+--------+-)-><
                                                        '-,flags-'

source-string:検索対象文字列
pattern:正規表現パターン
replacement-string:置換文字列
flags:正規表現オプション(省略可)

使用例

WITH DATA(TEXT) AS (VALUES 'ABCDEF','GHIJKL','UVWXYZ','',NULL)
SELECT TEXT,REG_REPLACE(TEXT,'(.)(D|X)(.)','$3_$2_$1') FROM DATA;

TEXT  2
------ --------
ABCDEF ABE_D_CF
GHIJKL GHIJKL
UVWXYZ UVY_X_WZ
               
-      -

REG_COUNT関数

機能
検索対象文字列に、指定された正規表現パターンに一致する部分が何回存在するかを戻すスカラー関数です。
一致する部分が存在しなかった場合、0が戻されます。
正規表現パターンには、副次式の参照として\1～\9が使用できます。
OracleのREGEXP_COUNT関数(*9)に相当する機能として使用可能です。

構文

>>-REG_COUNT(--source-string--,--pattern--+----------+--)----><
                                          '-,--flags-'

source-string:検索対象文字列
pattern:正規表現パターン
flags:正規表現オプション(省略可)

使用例

WITH DATA(TEXT) AS (VALUES 'ABCABC','GHI','XYZXYZXYZ','',NULL)
SELECT TEXT,REG_COUNT(TEXT,'A.{2}|X.{2}') FROM DATA;

TEXT     2
-------- --
ABCABC    2
GHI       0
XYZXYZXYZ 3
          0
-         0

REG_POSITION関数

機能
検索対象文字列に、指定された正規表現パターンに一致する部分が現れる開始位置(1オリジン、文字数単位)を戻すスカラー関数です。
一致する部分が存在しなかった場合、0が戻されます。
正規表現パターンには、副次式の参照として\1～\8が使用できます。(\9は使用不可)
OracleのREGEXP_INSTR関数(*10)に相当する機能として使用可能です。

構文

.-,--1---.
>>-REG_POSITION(--source-string--,--pattern--+--------+-+----------+--)----><
                                             '-,--seq-' '-,--flags-'

source-string:検索対象文字列
pattern:正規表現パターン
seq:検知番号(省略可,デフォルト1)
flags:正規表現オプション(省略可)

使用例

WITH DATA(TEXT) AS (VALUES 'ABCDEF','GHIJKL','UVWXYZ','',NULL)
SELECT TEXT,REG_POSITION(TEXT,'[BEHVY]'),REG_POSITION(TEXT,'[BEHVY]',2) FROM DATA;
TEXT   2   3  
------ --- ---
ABCDEF   2   5
GHIJKL   2   0
UVWXYZ   2   5
         0   0
-        0   0

REG_SUBSTR関数

機能
検索対象文字列の、指定された正規表現パターンに一致する部分を戻すスカラー関数です。
一致する部分が存在しなかった場合、NULLが戻されます。
正規表現パターンには、副次式の参照として\1～\8が使用できます。(\9は使用不可)
OracleのREGEXP_SUBSTR関数(*11)に相当する機能として使用可能です。

構文

>>-REG_SUBSTR(--source-string--,--pattern--+--------+-+----------+--)----><
                                           '-,--seq-' '-,--flags-'

source-string:検索対象文字列
pattern:正規表現パターン
seq:検知番号(省略可,デフォルト1)
flags:正規表現オプション(省略可)

使用例

WITH DATA(TEXT) AS (VALUES 'ABCDEF','GHIJKL','UVWXYZ','',NULL)
SELECT TEXT,REG_SUBSTR(TEXT,'[BEHVY]'),REG_SUBSTR(TEXT,'[BEHVY]',2)
FROM DATA;

TEXT   2     3    
------ ----- -----
ABCDEF B     E    
GHIJKL H     -    
UVWXYZ V     Y    
       -     -    
-      -     -

REG_SUBSTR_TABLE関数

機能
検索対象文字列に、指定された正規表現パターンに一致する部分及びその文字列の情報を戻す表関数です。
表関数のためFROM句内のみ使用可能で、常にTABLE()で囲む必要が有ります。
戻される表には連番の"SEQ"列、一致する部分の開始位置(1オリジン、文字数単位)の"POS"列、長さ(文字数単位)"LEN"列、そして一致する部分の文字列"TOKEN"列があります。
一致する部分が存在しなかった場合、行は戻されません。
正規表現パターンには、副次式の参照として\1～\8が使用できます。(\9は使用不可)

構文

>>-REG_SUBSTR_TABLE(--source-string--,--pattern--+----------+--)----><
                                                 '-,--flags-'

source-string:検索対象文字列
pattern:正規表現パターン
flags:正規表現オプション(省略可)

使用例

WITH DATA(TEXT) AS (VALUES 'ABCDEFB','EGHIJKL','MNOPQR','UVWXYZ','',NULL)
SELECT * FROM DATA,TABLE(REG_SUBSTR_TABLE(TEXT,'[BEHVY]'));

TEXT    SEQ  POS  LEN  TOKEN
------- ---- ---- ---- -----
ABCDEFB    1    2    1 B    
ABCDEFB    2    5    1 E    
ABCDEFB    3    7    1 B    
EGHIJKL    1    1    1 E    
EGHIJKL    2    3    1 H    
UVWXYZ     1    2    1 V    
UVWXYZ     2    5    1 Y

REG_TOKENIZE_TABLE関数

機能
検索対象文字列に、指定された正規表現パターンで一致する部分を区切り文字列として、一致しなかった部分及びその文字列の情報を戻す表関数です。
表関数のためFROM句内のみ使用可能で、常にTABLE()で囲む必要が有ります。
戻される表には連番の"SEQ"列、一致しなかった部分の開始位置(1オリジン、文字数単位)の"POS"列、長さ(文字数単位)の"LEN"列、そして文字列の"TOKEN"列が含まれています。
正規表現パターンに最初、最後、または連続して一致した場合、"TOKEN"列が0長文字列の行が出力されます。
正規表現パターンには、副次式の参照として\1～\8が使用できます。(\9は使用不可)

構文

>>-REG_TOKENIZE_TABLE(--source-string--,--pattern--+----------+--)----><
                                                   '-,--flags-'

source-string:検索対象文字列
pattern:正規表現パターン
flags:正規表現オプション(省略可)

使用例

WITH DATA(TEXT) AS (VALUES 'ABCDEFB','EGHIJKL','MNOPQR','',NULL)
SELECT *FROM DATA,TABLE(REG_TOKENIZE_TABLE(TEXT,'[BEH]'));

TEXT    SEQ  POS  LEN  TOKEN
------- ---- ---- ---- -------
ABCDEFB    1    1    1 A      
ABCDEFB    2    3    2 CD     
ABCDEFB    3    6    1 F      
ABCDEFB    4    8    0        
EGHIJKL    1    1    0        
EGHIJKL    2    2    1 G      
EGHIJKL    3    4    4 IJKL   
MNOPQR     1    1    6 MNOPQR 
           1    1    0        
-          1    -    - -

REG_ALLTOKEN_TABLE関数

機能
検索対象文字列に、指定された正規表現パターンで一致する部分及び、一致しなかった部分の文字列情報を戻す表関数です。
表関数のためFROM句内のみ使用可能で、常にTABLE()で囲む必要が有ります。
戻される表には連番の"SEQ"列、各部分の開始位置(1オリジン、文字数単位)の"POS"列、長さ(文字数単位)の"LEN"列、一致した部分か否かを表す"MATCH"列(一致：1、不一致：0)、そして文字列の"TOKEN"列が含まれています。
連続して正規表現パターンに一致した場合、"MATCH"列が1の列が連続して出力されます。
正規表現パターンには、副次式の参照として\1～\8が使用できます。(\9は使用不可)

構文

>>-REG_ALLTOKEN_TABLE(--source-string--,--pattern--+----------+--)----><
                                                   '-,--flags-'

source-string:検索対象文字列
pattern:正規表現パターン
flags:正規表現オプション(省略可)

使用例

WITH DATA(TEXT) AS (VALUES 'ABCDEFB','EGHIJKL','MNOPQR','',NULL)
SELECT * FROM DATA,TABLE(REG_ALLTOKEN_TABLE(TEXT,'[BEH]'));

TEXT    SEQ  POS  LEN  MATCH  TOKEN
------- ---- ---- ---- ------ ------
ABCDEFB    1    1    1      0 A     
ABCDEFB    2    2    1      1 B     
ABCDEFB    3    3    2      0 CD    
ABCDEFB    4    5    1      1 E     
ABCDEFB    5    6    1      0 F     
ABCDEFB    6    7    1      1 B     
EGHIJKL    1    1    1      1 E     
EGHIJKL    2    2    1      0 G     
EGHIJKL    3    3    1      1 H     
EGHIJKL    4    4    4      0 IJKL  
MNOPQR     1    1    6      0 MNOPQR
           1    1    0      0       
-          1    -    -      0 -

patternで使用できる正規表現について

副次式(括弧で囲まれた部分)にする事で同じ正規表現内から参照できます。例えばREG_COUNT('ABCABC_XYZXYZ_ABCXYZ','(.{3})\1')の戻り値は2です。これは、任意の3文字が2回連続するパターンを検出する正規表現になります。

副次式の参照は、\1～\9（または、\1～\8）が使用できます。副次式の参照の後に数字を書く場合は括弧で括る事("\11"ではなく"(\1)1")が推奨されています。ただしその場合の括弧も副次式として扱われます。

数値(\d)等のメタキャラクターやUnicode文字プロパティ(\p{})も使用できます。また、最長一致の欲張りアルゴリズム(*,+,?,{m,n})の他に最短一致の控えめアルゴリズム(*?,+?,??,{m,n}?) が使用できます。ただし前方一致(?=式)、後方一致(?<=式)等は使用できません。

この他、使用できる正規表現の詳細については、XQueryのマニュアル内の『XQueryの正規表現について』をご覧下さい。

replacement-stringについて

patternで副次式としたものが、$1～$9として参照できます。後に数字を書く場合にも、そのまま$11と書いて下さい。(こちらは、括弧で括る事はできません)
replacement-stringは、正規表現ではないのでメタキャラクターは使用できません。改行等制御文字を使用したい場合は、16進表記を使用して下さい。

16進表記について

pattern及びreplacement-stringで16進表記が必要な場合、通常のSQLと同様に文字ストリング定数(X’FF’、U&’\FFFF’)又はCHR関数を使用して下さい。ただし、文字コードの変換には注意が必要です。

XMLやXQueryの文字参照(&#FF;)やエンティティー参照(<、&)は使用できません(通常の文字列として扱われます)。

flagsオプションについて

"s","m","i","x"の4文字の有無で、正規表現の動きが変わります。
例えば、改行が含まれている文字列を検索する場合に"^"や"$"を文字列全体の先頭や末尾ではなく改行文字で区切られた行単位に検出させる為には"m"の指定が必要です。

表2. flagsオプション一覧

フラグ	説明
s	ドット(.)について改行文字(\n,\r)を含めて検出する場合に指定
m	脱字記号(^)とドル記号($)を文字列の先頭や末尾ではなく文字列内の行単位の検出に使用する場合に指定
i	大/小文字を区別しないで検出する場合に指定
x	pattern 内の空白文字を無視する場合に指定

上に戻る

導入方法

インストール

各正規表現UDFを導入するには、添付ファイルのDDLを実行して下さい。
ダウンロードした添付ファイルを任意の場所に解凍し、DB2 CLP（コマンド行プロセッサー）で『01_CREATE_REG_UDF_DLL.txt』を実行して下さい。(SQLステートメント終了文字は”#”です)
UDFは、ログインしたユーザのスキーマオブジェクトとして作成されます。

リスト1. インストール実行例

C:\Program Files\IBM\SQLLIB\BIN>cd G:\DL

C:\DL>dir /b
01_CREATE_REG_UDF_DLL.txt
02_GRANT__REG_UDF_DCL.txt
03_DROP___REG_UDF_DDL.txt

C:\DL>db2 connect to sample
C:\DL>db2 -td# -vf 01_CREATE_REG_UDF_DLL.txt
C:\DL>db2 terminate

権限付与

各正規表現UDFを作成したユーザ以外でも使用する場合は、個別のユーザもしくはPUBLICへのEXECUTE特権付与が必要です。PUBLICにEXECUTE特権を付与する場合、DB2 CLPで『02_GRANT__REG_UDF_DCL.txt』を実行して下さい。（インストール時と同じユーザでログインして下さい）

リスト2. PUBLIC EXECUTE特権付与例

C:\Program Files\IBM\SQLLIB\BIN>cd C:DL

C:\DL>dir /b
01_CREATE_REG_UDF_DLL.txt
02_GRANT__REG_UDF_DCL.txt
03_DROP___REG_UDF_DDL.txt

C:\DL>db2 connect to sample
C:\DL>db2 -td# -vf 02_GRANT__REG_UDF_DCL.txt
C:\DL>db2 terminate

アンインストール

正規表現UDFが不要になった場合、UDFをDROPして下さい。DB2 CLPで『03_DROP___REG_UDF_DDL.txt』を実行して下さい。（インストール時と同じユーザでログインして下さい）

リスト3. アンインストール実行例

C:\Program Files\IBM\SQLLIB\BIN>cd C:DL

C:\DL>dir /b
01_CREATE_REG_UDF_DLL.txt
02_GRANT__REG_UDF_DCL.txt
03_DROP___REG_UDF_DDL.txt

C:\DL>db2 connect to sample
C:\DL>db2 -td# -vf 03_DROP___REG_UDF_DDL.txt
C:\DL>db2 terminate

上に戻る

Oracleの正規表現関数との比較

Oracleの組み込み正規表現関数と正規表現UDFの対応は以下の通りです。オプション引数の有無などの細かな違いはありますが、Oracle Database 11gでサポートされている５つの関数すべてに対応するUDFを提供しています。ただし、正規表現等の引数指定に互換性はありません。なお、UDFで提供している表関数に該当する、Oracleの組み込み関数は存在しません。

表3. 正規表現UDFとOracleの正規表現関数との対応一覧

Oracle組み込み関数	対応するUDF名	機能
REGEXP_LIKE	REG_MATCHES	一致有無確認
REGEXP_REPLACE	REG_REPLACE	文字列置換
REGEXP_COUNT	REG_COUNT	一致回数取得
REGEXP_INSTR	REG_POSITION	一致位置取得
REGEXP_SUBSTR	REG_SUBSTR	一致文字列取得

※OracleのREGEXP_LIKEは関数ではなく条件(コンディション)の扱いです。

上に戻る

注意点

文字コード変換について

DB2 9のpureXML機能はすべてUTF-8として処理される為、Shift-JIS等のUTF-8以外のデータベースの場合相互の文字コード変換が発生しています。例えば複数のコードポイントを持つ文字(ローマ数字等)が存在する場合、統一されたコードポイントで戻ってきます。
なおv9.1ではUTF-8以外のデータベースからPureXML機能の使用はできません。

Oracle(VARCHAR2)互換機能について

v9.7のOracle(VARCHAR2)互換機能がONの場合には0長文字列はNULLとして扱われます、ご注意下さい。

パフォーマンスについて

パフォーマンスの観点で検証していませんが、通常問題となるlike等での一致検索に比べても構造的に劣る可能性があります。曖昧検索機能としてシステム的に提供する場合、十分な検証が必要だと思われます。C言語のUDF導入も検討してみて下さい。データの絞り込みはREG_MATCHES関数が適しています。

pureXMLのライセンスについて

DB2 9のpureXML機能は、リリース当初は有料フューチャーとなっていましたが、現在は無料フューチャーの扱いに変わった為、pureXML機能を使われていない方でも、紹介したUDFは問題無く使用可能と思われます。(ライセンス上の問題は各自で十分確認して下さい)

上に戻る

おわりに

紹介したUDFによって、簡単にSQL上で正規表現が使用できる事がご理解頂けましたでしょうか。既に正規表現に慣れている方はデータの調査やデータのクリーニング等に是非使ってみて下さい。正規表現をよく知らない方も、この機会に是非覚えてみて下さい。

UDFが期待通りに動作しない場合、XQuery上で正規表現の動作を確認して下さい。紹介したUDFの使用に関して、自己責任でお願いします。

上に戻る

ダウンロード