今回の記事は、SQL-Server 2008 Expressにおいて、正規表現を使って文字列をリプレース(置換)する関数を、SQLCLRで作ってみます。
前回の記事では、元々SQL-Serverに備わっている文字列検索用のlikeの代わりに、正規表現で文字列パターンを判定してデータを抽出するSQLCLR_Regex()という関数を作りました。
しかし、likeの時もそうなんですが、データが抽出できるのは良いのですが、その先が問題です。
たとえば、抽出したデータを他の文字列でリプレース(置換)したいというのは結構ありがちだと思うのですが、これがまたやっかいだと思います。
そこで、それを実現する関数をSQLCLRで作ってみます。
今回の関数名は、SQLCLR_RegexReplace()としてみます。
ちなみに、処理の一部をローカル関数にしていますが、他の処理でも使いたいので共通化の意味でばらしました。
SQLCLRの登録などは過去の記事をご参考になさって下さい
■実行例■
--ゲーム名テーブル#T_BASEにおいて、
--ゲーム名の末尾に「伝説」か「物語」のどちらかがつくものを抽出し、
--「伝説」と「物語」を「○」でリプレースする。
--
--※条件はSQL_Regex()を使っています。
SELECT
GAME_NAME AS [元のゲーム名],
dbo.SQLCLR_RegexReplace(GAME_NAME,'○','(伝説|物語)$') AS [リプレース後のゲーム名]
FROM #T_GAME
WHERE dbo.SQLCLR_Regex(GAME_NAME,'(伝説|物語)$') = 1;
■コード■
(インデントは消えています)
(無償版のVisual C# 2008でプログラムを書けます)
(全角の＜と＞は、半角に読み替えて下さい)
//*******************************************************************************
// * ローカル関数
// * SQLCLR_RegexSelectと共通で使います。
//*******************************************************************************
//------------------------------------
//正規表現で取得したパターンをList＜＞で返却
//p_value:検査したい文字列
//p_pat :正規表現パターン
//------------------------------------
private static List＜string＞ GetMatchList(
string p_value,
string p_pat
)
{
//結果格納エリア
List＜string＞ list_str_result = new List＜string＞();
//正規表現コア
Regex obj_rx;
//正規表現マッチコレクション
MatchCollection col_match;
//正規表現マッチ結果格納配列
string[] strArr_match;
//比較エリア
string str_comp_match = "";
//インデックス
Int32 i;
try
{
obj_rx = new Regex(p_pat);
col_match = obj_rx.Matches(p_value);
if (col_match.Count ＞ 0)
{
//配列要素数決定
strArr_match = new string[col_match.Count];
//取得したマッチ文字列を配列に格納
//@@@@@@@@@@[LOOP-START]@@@@@@@@@@
for (i = 0; i ＜ col_match.Count; i++)
{
strArr_match[i] = col_match[i].Value;
}
//@@@@@@@@@@[LOOP-END ]@@@@@@@@@@
//配列をソート
Array.Sort(strArr_match);
//@@@@@@@@@@[LOOP-START]@@@@@@@@@@
for (i = 0; i ＜ strArr_match.Length; i++)
{
//マッチ文字列のうち、重複していない文字列だけを結果にセット
if (!(str_comp_match.Equals(strArr_match[i])))
{
list_str_result.Add(strArr_match[i]);
str_comp_match = strArr_match[i];
}
}
//@@@@@@@@@@[LOOP-END ]@@@@@@@@@@
}
}
catch (System.Exception obj_err)
{
throw obj_err;
}
return list_str_result;
}
//*******************************************************************************
//* SQL関数
//*******************************************************************************
//------------------------------------
//正規表現による文字列リプレース
//------------------------------------
//using System.Text.RegularExpressions;を記述して下さい。
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString SQLCLR_RegexReplace(
SqlString p_value,
SqlString p_replace,
SqlString p_pat
)
{
//マッチ結果取得エリア
List＜string＞ list_str_value = new List＜string＞();
//結果格納エリア
string str_result = "";
//置き換えエリア
string str_replace;
//インデックス
Int32 i;
try
{
//SQL-ServerからNULLが来ない時に処理する。
if (!(p_value.Equals(SqlString.Null)))
{
str_result = (string)p_value;
str_replace = (string)p_replace;
//自作関数GetMatchListで正規表現にマッチした文字列をList＜＞で取得
list_str_value = GetMatchList(str_result, (string)p_pat);
//マッチした文字列分をリプレースする。
//@@@@@@@@@@[LOOP-START]@@@@@@@@@@
for (i = 0; i ＜ list_str_value.Count; i++)
{
str_result = str_result.Replace(list_str_value[i], str_replace);
}
//@@@@@@@@@@[LOOP-END ]@@@@@@@@@@
}
}
catch (System.Exception obj_err)
{ //例外が発生したらSQL-Serverに投げつけて強制終了させる。
throw obj_err;
}
return (SqlString)str_result;
}
■SQL-Server 2008 Express側のインターフェース■
(環境はご自分のものに読み替えお願いします。)
-- ----------------------------------------------
-- 正規表現による文字列リプレース
-- (パラメータ説明)
-- @p_value:判定したい文字列
-- @p_replace:リプレースしたい文字列
-- @p_pat:リプレースするための正規表現パターン
-- ----------------------------------------------
create function SQLCLR_RegexReplace(
@p_value nvarchar(max),
@p_replace nvarchar(max),
@p_pat nvarchar(max)
)
RETURNS nvarchar(max)
AS EXTERNAL NAME ASSEMBLY_SQLCLR.CLS_SQLCLR.SQLCLR_RegexReplace;
GO
