今回の記事は、SQL-Server 2008 Expressにおいて、文字列を「文字単位」ではなくて「バイト数単位」で切り出す関数を作ってみます。
実はこれ、以前当ブログでやっていた記事の繰り返しだったりします。
この機能を今後応用して行く記事を書かせていただく予定があるのと、当時よりも少しコードを改善したため、再び今回の記事とあいなりました次第です。
改善点は、SQL-Server 2008からNullデータが入ってきた時にもエラーにならない点です。
プログラムの構成は、合計5本です。
まずは、直接SQL関数と紐付けて利用するSQLCLR関数が3本。
(1)任意の位置をバイト数単位で切り出せるSQLCLR_SubStringB()
(2)右からバイト数単位で切り出せるSQLCLR_RightB()
(3)左からバイト数単位で切り出せるSQLCLR_LeftB()
次は、内部的な共通関数が2本。
(1)処理のコアに当たるプライベート関数fnc_Core_StringB()
(2)全角文字が半分に断ち割られた時に対応するためのfnc_Create_SJIS_StatusArray()
です。
全角文字をバイト数単位で切り出す時にやっかいなのが、半分で断ち割られた時にどうするか?
という問題です。
半分に断ち割られた文字が、意図しないものに文字化けしてゴミとして表示されてしまうわけです。
このゴミを半角スペースで補正するのが今回の処理の肝の一つになっています。
ここの手法は、私が力技的に無理やり考えたものなので、スマートとは言えませんけれど・・・。
ご容赦下さい。
なお、今回の実行例では、64bit版での利用が可能になった事を利用してさっそくExcelのワークシートからデータを読み込むようにしています。
SQLCLRの登録などは過去の記事をご参考になさって下さい。
■実行例■
Microsoft.ACE.OLEDB.12.0プロバイダについては以前の記事をご参考になさって下さい。
--実行例
--(データはExcelワークシートから直接読み込むようにしています。)
SELECT
M.F2 AS [元々の項目],
dbo.SQLCLR_SubStringB(M.F2,1,10) AS [2桁目から10バイト切り出し],
dbo.SQLCLR_RightB(M.F2,10) AS [右から10バイト切り出し],
dbo.SQLCLR_LeftB(M.F2,10) AS [左から10バイト切り出し]
FROM OpenRowSet('Microsoft.ACE.OLEDB.12.0',
'Excel 8.0;DATABASE=C:\TEST\TEST.XLS;IMEX=1;HDR=NO',
'SELECT * FROM [Sheet1$]') AS M;
■コード■
(インデントは消えています)
(全角の＜＞は半角に読み替えお願いします。)
(無償版のVisual C# 2010でプログラムを書いています)
//*******************************************************************************
// * ローカル関数
//*******************************************************************************
//*******************************************************
//*文字列をバイト単位で切り出すコア関数
//*(パラメータ)p_value=文字列,p_start=開始位置(0スタート)、長さ
//*******************************************************
private static string fnc_Core_StringB(string p_value,Int32 p_start, Int32 p_length)
{
List＜byte＞ bytList_substring = new List＜byte＞(); //文字列バイナリの切り出しエリア
string str_result = ""; //結果格納エリア
byte[] bytArr_value; //文字列のバイナリ変換用配列
byte[] bytArr_status; //バイナリ変換された文字列と1:1で対応するステータス配列
Int32 int_start_location = 0; //開始位置
Int32 int_end_location = 0; //終了位置
Int32 i = 0; //インデックス
Encoding enc_sjis = Encoding.GetEncoding("Shift_JIS"); //Shift_JISのエンコーダー
try
{
bytArr_value = enc_sjis.GetBytes(p_value); //文字列をバイナリの配列に変換
//文字列のバイナリ配列と1:1で対応したShift_JISステータス配列を作成
//0x01=全角の1バイト目、0x02=全角の2バイト目、その他=0x00と設定されます。
bytArr_status = fnc_Create_SJIS_StatusArray(bytArr_value);
//開始位置設定
if (p_start ＜ 0)
{ //スタート位置がマイナスの時は0に補正
int_start_location = 0;
}
else
{
int_start_location = p_start;
}
//終了位置設定
int_end_location = int_start_location + p_length - 1;
if (int_end_location ＞ bytArr_value.Length - 1)
{ //終了位置が全体の最終位置を突破していたら、最終位置に補正
int_end_location = bytArr_value.Length - 1;
}
//切り出し先頭部分の調整
if (int_start_location ＞ 0)
{ //切り出し先頭が0より大きい場合検査開始。
if (bytArr_status[int_start_location] == 0x02) //この16進数値はfnc_Create_SJIS_StatusArray()で設定済
{ //切り出し先頭が、全角を断ち割った2バイト目にかかっていた場合は補正
bytArr_value[int_start_location] = 0x20; //半角スペース(16進数)で補正
}
}
//切り出し末尾部分の調整
if(int_end_location ＜ bytArr_value.Length-1)
{ //末尾が全体の最終位置でなかった場合検査開始。
if (bytArr_status[int_end_location] == 0x01) //この16進数値はfnc_Create_SJIS_StatusArray()で設定済
{ //切り出し末尾が、全角を断ち割った1バイト目にかかっていた場合は補正
bytArr_value[int_end_location] = 0x20; //半角スペース(16進数)で補正
}
}
//切り出し末尾部分の調整
//@@@@@@@@@@[LOOP-START]@@@@@@@@@@
for (i = int_start_location; i ＜ int_end_location + 1; i++)
{
bytList_substring.Add(bytArr_value[i]); //文字列のバイナリを再構成
}
//@@@@@@@@@@[LOOP-END ]@@@@@@@@@@
//バイナリから文字列に変換
str_result = enc_sjis.GetString(bytList_substring.ToArray());
}
catch (System.Exception obj_err)
{
throw obj_err;
}
return str_result;
}
//*******************************************************
// Shift_JIS文字列のバイナリ配列と1:1対応のステータス配列作成
// 全角文字の1バイト目=0x01、2バイト目=0x02、その他=0x00で設定
//*******************************************************
private static byte[] fnc_Create_SJIS_StatusArray(byte[] bytArr_value)
{
byte[] bytArr_result = new byte[bytArr_value.Length]; //ステータス配列
Int32 i = 0; //インデックス
Int32 int_zenkaku_flg = 0; //Shift_JIS全角文字出現フラグ
try
{
//@@@@@@@@@@[LOOP-START]@@@@@@@@@@
for (i = 0; i ＜ bytArr_value.Length; i++)
{
if (int_zenkaku_flg == 1)
{ //前回、全角の1バイト目が出現したので今回は必ず2バイト目
bytArr_result[i] = 0x02;
int_zenkaku_flg = 0; //全角出現フラグのクリア
}
else if((bytArr_value[i] ＞= 0x81 && bytArr_value[i] ＜= 0x9F) ||
(bytArr_value[i] ＞= 0xE0 && bytArr_value[i] ＜= 0xFF)
)
{ //全角文字の1バイト目が出現した
bytArr_result[i] = 0x01;
int_zenkaku_flg = 1;
}
else
{ //全角でない場合
bytArr_result[i] = 0x00;
}
}
//@@@@@@@@@@[LOOP-END ]@@@@@@@@@@
}
catch (System.Exception obj_err)
{
throw obj_err;
}
return bytArr_result;
}
//*******************************************************************************
//* SQL関数
//*******************************************************************************
//*******************************************************
//public class CLS_SQLCLRの中にあります。
//バイト単位での文字列抽出
//*******************************************************
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString SQLCLR_SubStringB(SqlString p_value, SqlInt32 p_start, SqlInt32 p_length)
{
string str_result = ""; //結果格納エリア
try
{
if (!(p_value.Equals(SqlString.Null)))
{
str_result = fnc_Core_StringB((string)p_value, (Int32)p_start, (Int32)p_length);
}
}
catch (System.Exception obj_err)
{
str_result = obj_err.Message;
}
return (SqlString)str_result;
}
//*******************************************************
//public class CLS_SQLCLRの中にあります。
//バイト単位で右側から文字列を切り出し
//*******************************************************
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString SQLCLR_RightB(SqlString p_value, Int32 p_length)
{
string str_result = "";
Int32 int_start_location = 0;
try
{
if (!(p_value.Equals(SqlString.Null)))
{
int_start_location = Encoding.GetEncoding("Shift_JIS").GetBytes((string)p_value).Length
- (Int32)p_length;
str_result = fnc_Core_StringB((string)p_value, int_start_location, (Int32)p_length);
}
}
catch (System.Exception obj_err)
{
throw obj_err;
}
return str_result;
}
//*******************************************************
//public class CLS_SQLCLRの中にあります。
//バイト単位で左側から文字列を切り出し
//*******************************************************
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString SQLCLR_LeftB(SqlString p_value, Int32 p_length)
{
string str_result = "";
try
{
if (!(p_value.Equals(SqlString.Null)))
{
str_result = fnc_Core_StringB((string)p_value, 0, (Int32)p_length);
}
}
catch (System.Exception obj_err)
{
throw obj_err;
}
return str_result;
}
■SQL-Server関数インターフェース■
(環境はご自分のものと読み替えお願いいたします)
-- ----------------------------------------------
-- 文字列を任意の位置でバイト単位の切り出しを行う
-- (パラメータ説明)
-- @p_value :文字列
-- @p_start :スタート位置(0スタート)
-- @p_length:長さ
-- ----------------------------------------------
CREATE FUNCTION SQLCLR_SubStringB(@p_value nvarchar(max),@p_start int,@p_length int)
RETURNS nvarchar(max)
AS EXTERNAL NAME ASSEMBLY_SQLCLR.CLS_SQLCLR.SQLCLR_SubStringB;
GO
-- ----------------------------------------------
-- 文字列を右側から指定バイト分切り出す
-- (パラメータ説明)
-- @p_value :文字列
-- @p_length:長さ
-- ----------------------------------------------
CREATE FUNCTION SQLCLR_RightB(@p_value nvarchar(max),@p_length int)
RETURNS nvarchar(max)
AS EXTERNAL NAME ASSEMBLY_SQLCLR.CLS_SQLCLR.SQLCLR_RightB;
GO
-- ----------------------------------------------
-- 文字列を左側から指定バイト分切り出す
-- (パラメータ説明)
-- @p_value :文字列
-- @p_length:長さ
-- ----------------------------------------------
CREATE FUNCTION SQLCLR_LeftB(@p_value nvarchar(max),@p_length int)
RETURNS nvarchar(max)
AS EXTERNAL NAME ASSEMBLY_SQLCLR.CLS_SQLCLR.SQLCLR_LeftB;
GO
