しのもりのエンジニアっぽいやつのほう: SQLSERVERで全角半角変換するストアド(C#なDLLを使用)

シリーズ目次

その3：PowerShellでSelect結果をCSV出力
その4：PowerShellでテーブルCreateスクリプトを出力

前置き

今回は、SQLSERVERで全角半角変換するストアド(C#なDLLを使用)を紹介します！
SQLを採用するどのRDBMSでも、半角カタカナ→全角カタカナの変換はなかなかサポートされていません(Oracleだとできるみたい)。
まともにストアドを書くと、1文字ずつ見て変換するような形になります。

しかし一方、VisualBasicには全角半角変換できる「StrConv」があります。

これを使えるようにできればかなり便利なのでは！？
今回はC#で書いたStrConvをDLLにして、それをSQLCLRという機能を有効化することでSQLSERVERに登録、スカラー関数化してみます！

MSDN的にはこちらを御覧ください。
CLR 関数の作成
clr enabled サーバー構成オプション

準備

SQLCLRの有効化
DLLの用意
DLLの登録、スカラー関数の作成
実行

SQLCLRの有効化

SQLSERVERで下記ストアドを実行します。

	ALTER DATABASE data_convert_to SET TRUSTWORTHY ON;

	EXEC sp_configure 'clr enabled', 1;
	GO
	RECONFIGURE;
	GO

view raw enable_SQLCLR.sql hosted with ❤ by GitHub

「data_convert_to」の部分は、スカラー関数を作成したいデータベース名を書いてください。

DLLの用意

VisualStudioの「新しいプロジェクトの作成」を開いて、「クラスライブラリ(.Net Framework)」を選択してください。(.Net Standard)ではないので注意。

プロジェクトを作成したら、参照で「Microsoft.VisualBasic」が参照されていることを確認してください。
下記のようにコーディング。

	using Microsoft.SqlServer.Server;

	namespace ConvertStringsSQLCLR
	{
	public class ConvertStrings
	{
	[SqlFunction]
	public static string StringUpper(string target)
	{
	return Microsoft.VisualBasic.Strings.StrConv(target, Microsoft.VisualBasic.VbStrConv.Wide);
	}
	}
	}

view raw ConvertStringsSQLCLR.cs hosted with ❤ by GitHub

ビルドしてDLLを作成します。

DLLの登録、スカラー関数の作成

作成したDLLをアセンブリ→スカラー関数としてSQLSERVERに登録。

	CREATE ASSEMBLY [ConvertStringsSQLCLR]
	FROM 'C:\data_converter\dll\source\ConvertStringsSQLCLR\ConvertStringsSQLCLR\bin\Release\ConvertStringsSQLCLR.dll'
	WITH PERMISSION_SET = SAFE;
	GO

	CREATE FUNCTION ConvertStringsSQLCLR( @str nvarchar(max)) returns nvarchar(max)
	AS EXTERNAL NAME ConvertStringsSQLCLR.[ConvertStringsSQLCLR.ConvertStrings].StringUpper;
	GO

view raw regist_DLL.sql hosted with ❤ by GitHub

成功するとこんな感じで登録されます。

実行例

試しに実行してみます。
「ｱイｳエｵカｷクｹコ！!［[」というレコードを変換します。

変換前
ｱイｳエｵカｷクｹコ！!［[
→
変換後
アイウエオカキクケコ！！［［

完璧に変換されていますね！！
SQLSERVERだけで実装できなくなるのが辛いところですが、この方法ならば、SQLSERVERではカバーできていないいろいろな関数を組み込むことが可能です。

次回

その3：PowerShellでSelect結果をCSV出力