SQL Server で文字列の半角／全角変換

SQL Server 2005 のサーバー側の関数で，文字列の半角／全角変換をしたいとずっと思っていました。ネットでググると，「SQL Server にはその機能がないので，REPLACE関数を羅列して解決するしかない」という記事（データの全角半角変換、、 － Database Expert － ＠IT）に当たり，そのコードを実装した記事（SQLServerでの文字列の半角文字から全角文字への変換）に当たりました。確かにT-SQLで実装しようとすると，その方法しかないでしょう。しかし，あまりにあまりな力業(^^;

一方，SQL Server 2005 からはSQLCLRというのが使えるはずです。それを使えばVB.NETで書いた関数を，サーバーサイドの関数にできるはず。AccessのMDBを使っている場合には，クエリの中でAccess VBAの関数を使えるので，StrConv関数を使って簡単に実現できていました。StrConvは.NETでも使えます。SQLCLRを一度やってみたいと思いながら手をつけずにいたので，いっちょ挑戦してみるかという気分になりました。そう思ってさらにググってみると，次の記事を発見しました。

ノラの日々 : ＳＱＬ ＣＬＲでSTRCONV関数

この記事がまさに私の求めていたものです。さっそくマネマネすることにしました。

SQLCLR構成オプションの有効化

まず，SQLCLRを使えるようにする必要があります。その方法は「SQL Server 自習書 開発編 No.3 SQLCLR」に載っていますが，簡単に説明すると次の手順です。

1. 全てのプログラム > Microsoft SQL Server 2005 > 構成ツール > SQL Server セキュリティ構成を実行。
2. 「機能のセキュリティ構成」をクリック。
3. ツリーの中から「データベースエンジン」を展開して「CLR統合」をクリック。
4. 「CLR統合を有効にする」のチェックを入れて「適用」ボタンをクリック。

DLLの作成

次にVBで書いたプログラムソースを入力します。先ほどのサイトに掲載されていたVBのソース部分です。

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
 
Public Class SQLCLR
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function SqlStrConv(ByVal str As SqlString, ByVal mode As SqlInt32) As SqlString
        Return StrConv(str.ToString, mode)
    End Function
End Class

このプログラムをSqlClrStrConv.vbというファイル名で作成します。ここでは C:\CLRLib というフォルダに作成することにします。次にコマンドプロンプトを起動してC:\CLRLibに行きます。なお，ここでの手順ではこれらの作業はサーバー上で行います。

C:\>CD CLRLib

私の環境ではVBコンパイラ(VBC.exe)にパスが通っていませんでしたので，そこにパスを通します。.NET 2.0のコンパイラを使う必要があるようです。

C:\CLRLib>PATH C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\;%PATH%

パスはお使いの環境に合わせてくださいね。これでコンパイルの準備ができましたので，コンパイルします。

C:\CLRLib>vbc /out:SqlClrStrConv.dll /t:library /r:"C:\Program Files\Microsoft S
QL Server\MSSQL.1\MSSQL\Binn\sqlaccess.dll" SqlClrStrConv.vb

エラーなくコンパイルができたら，SqlClrStrConv.dllができていると思います。

ストアド関数の作成

次に，関数を作成するSQL文を実行します。関数を作成したいデータベースに対して実行してください。SQL Server Management Studio の場合は次のように

CREATE ASSEMBLY [SqlClrStrConv]
FROM 'C:\CLRLib\SqlClrStrConv.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION SqlStrConv( @str nvarchar(max),@conversion int ) returns nvarchar(max)
AS EXTERNAL NAME SqlClrStrConv.SQLCLR.SqlStrConv
GO

僕の好きなA5:SQLだと次のような感じで ; コロンで終了して，「すべてを順に」で実行します。

CREATE ASSEMBLY [SqlClrStrConv]
FROM 'C:\CLRLib\SqlClrStrConv.dll'
WITH PERMISSION_SET = SAFE;
CREATE FUNCTION SqlStrConv( @str nvarchar(max),@conversion int ) returns nvarchar(max)
AS EXTERNAL NAME SqlClrStrConv.SQLCLR.SqlStrConv;

同じサーバー上の他のデータベースで利用したい場合は，そのデータベースで上記のSQLを実行すれば使えるようになります。

関数を使う

実際にSQLで変換をするには次のようにします。

半角に変換

SELECT
    dbo.SqlStrConv(NameKana, 8)
FROM
    tblPersons

全角に変換

SELECT
    dbo.SqlStrConv(NameKana, 4)
FROM
    tblPersons

第二引数の値はVbStrConv 列挙型ですが，数値に直すとほぼVBAと同じです。

でも.NETのStrConvは，なんだか簡体字／繁体字の変換までできるみたいですね。