SQL CLR関数で全角と半角、ひらがなとカタカナの変換 [SQLServer]

SQLServerロゴ

SQLServerで全角文字と半角文字を相互変換する処理と、ひらがなとカタカナを相互変換する処理を行う関数を作成します。
SQLServerのシステム関数には上記の文字変換を行う関数がありませんので、.NET Frameworkでアセンブリ(dll)を作成し、それをSQLServerに登録してCLR関数として作成します。

SQLServerのCLR関数の作成の詳細については以下の記事を参照してください。

SQL Server 2005から提供されている.NET Frameworkの共通言語ランタイム(CLR: Common Langu...

アセンブリ(dll)の作成

まずはVisual Studioを使用してアセンブリ(dll)を作成します。

プロジェクトの作成

Visual StudioでC#またはVisual Basicでクラスライブラリのプロジェクトを新規作成します。

C#のクラスライブラリのプロジェクトを新規作成
ここではC#のクラスライブラリのプロジェクトを「SqlClrStrConv」という名前で新規作成しています。

全角と半角の変換と、ひらがなとカタカナの変換にはVisual BasicのStrConv関数を使用するので、プロジェクトをC#で作成している場合は、プロジェクトに参照を追加します。
ソリューションエクスプローラーのプロジェクトを展開して「参照」を右クリックして「参照の追加」をクリックします。

C#のプロジェクトに参照を追加

「参照マネージャー」のダイアログボックスが表示されますので、左ペインの「アセンブリ」をクリック(選択)し、「Visual Basic」を選択(チェックを付ける)して「OK」ボタンをクリックします。

C#のプロジェクトにVisual Basicの参照を追加

関数(メソッド)の実装

プロジェクトの準備ができたらSQLServerで使用する関数をメソッドで実装します。

C#での実装

C#の場合はusingディレクティブでMicrosoft.VisualBasicを追加しておきます。

Visual Basicでの実装

実装するメソッドをSQLServerの関数にするためにC#ではMicrosoft.SqlServer.Serverのusingを、Visual BasicではMicrosoft.SqlServer.ServerのImportsを追加します。
各メソッドにはSqlFunctionの属性を追加します。C#では[SqlFunction]Visual Basicでは<SqlFunction()>の形式で追加できます。

アセンブリ(dll)の作成

関数(メソッド)の実装ができたらプロジェクトをReleaseモードでビルドして、dllを作成します。

SQLServerに登録する文字列変換のアセンブリ(dll)

SQL CLR関数の作成

SQLServerで.NETで作成したアセンブリ(dll)を使用できるようにするための設定を行います。

アセンブリ(dll)の登録

作成したアセンブリをSQLServerに登録します。アセンブリの登録にはCREATE ASSENBLYステートメントを使用します。

CREATE ASSENBLYステートメントを実行する前に、アセンブリを登録するデータベースのTRUSTWORTHYプロパティをONに設定します。
この処理を実行しないとCREATE ASSENBLYステートメントでエラーが発生してしまいます。

上記のSQLが実行できれば、データベースにアセンブリが登録されますので、アセンブリのメソッドを使用したストアド関数を作成します。

CLR関数の作成

CREATE FUNCTIONステートメントで外部アセンブリを参照した関数を作成します。
関数のスクリプトにはEXTERNAL NAMEで作成したアセンブリのメソッドを指定します。

EXTERNAL NAMEではアセンブリ名.[名前空間.クラス名].メソッド名を指定します。

もし.NETで作成したアセンブリでCLR関数が作成できない場合は、以下の記事を参考に実装を見直してみてください。

SQL Server 2005から提供されている.NET Frameworkの共通言語ランタイム(CLR: Common Langu...

CLR関数の実行

作成したCLR関数を実行して正しく実装できているかを確認します。
Microsoft SQL Server Management Studioで以下のSQLスクリプトを実行します。

実行結果

全角、半角、ひらがな、カタカナを変換するCLR関数の実行結果

関数を実行するとそれぞれの処理が行われて結果が取得されました。