T-SQL: 全角を半角に変換する CLR 関数

SQL Server 内での全角と半角

SQL Server はデフォルトのコレーションのまま使っていると、文字幅センシティブではないので、半角と全角は同じとみなされます。

例えば、少しわかりにくいかもしれませんが、下の図ではデータベースに保存されているダニエルのＤは全角で、クエリーの WHERE 句の D は半角ですが、同じと判断されてしまっています。

データベース内ではいいかもしれませんが、クライアントプログラムでは別の値と判断されて、システムが思うように動かなくなる原因になる可能性があります。

SQL Server で全角データを半角に変換する組み込み関数などはなく、REPLACE 関数を使って文字をひとつひとつ指定して置き換えるなどしなくてはならず、簡単にはいきません。

そこで .NET Framework の Microsoft.VisualBasic の Strings.StrConv を使って、全角を半角に変換する CLR 関数を作ってみました。

※ CLR 関数の詳しい作り方はこちらをご覧ください。

1. dll ファイルを作成する

まず、Visual Studio でクラスライブラリのプロジェクトを作ります。 CLRUdf というプロジェクト名にしました。

※ 手元の Visual Studio の設定が C# 用になっているので、C# で作っています。

次のような StrConvToNarrow という名前のメソッドを作りました。

using System;
using System.Collections.Generic;
using System.Text;
using System.Threading.Tasks;
using Microsoft.SqlServer.Server;
using Microsoft.VisualBasic;

namespace CLRUdf
{
    public class CLRUdfStringHelper
    {
        [SqlFunction]
        public static string StrConvToNarrow(string inputString)
        {
            // LocaleID: 1041 - Japanese
            return Strings.StrConv(inputString, VbStrConv.Narrow, 1041); 
        }
    }
}

これをリリースモードでビルドして、 CLRUdf.dll を作っておきます。

2. SQL Server のアセンブリに登録する

以下のスクリプトを実行して、 CLRUdf.dll を SQL Server のアセンブリに登録します。

CREATE ASSEMBLY CLRUdf
FROM 'C:\Temp\CLRFunction\CLRUdf\bin\Release\CLRUdf.dll'
WITH PERMISSION_SET = SAFE;

3. CLR ファンクションを作成する

以下のスクリプトを実行して、先ほど登録したアセンブリを使った ufnCLRStrConvToNarrow という名前の CLR 関数を作成します。

CREATE FUNCTION ufnCLRStrConvToNarrow(
    @InputString NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)   
AS EXTERNAL NAME CLRUdf.[CLRUdf.CLRUdfStringHelper].StrConvToNarrow;

EXTERNAL NAME の後は SQL Server 側のアセンブリ名.[ネームスペース名.クラス名].メソッド名です。

4. 「 clr enabled 」サーバー構成オプションを有効化する

以下のスクリプトを実行して、clr enabled オプションを有効化し、SQL Server でアセンブリを実行できるようにします。

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

このオプションが無効になっていると CLR 関数を使った時に「.NET Framework でのユーザーコードの実行は無効です。"clr enabled" 構成オプションを有効にしてください。」というエラーが出ます。

5. CLR 関数を使ってみる

それでは、次のスクリプトを実行して作った CLR 関数を使ってみます。

DECLARE @InputString NVARCHAR(MAX) = N'ＡＢＣＤＥ’＃＃＄＄＠（）あア';

SELECT  @InputString,
        dbo.ufnCLRStrConvToNarrow(@InputString);

[ 実行結果 ]

半角がある文字は全角から半角に変換されていますね。

最初に例としてとりあげた、Employee テーブルの EmployeeName もこのように変換後の値を取得できるので、このファンクションを使って UPDATE 文で上書きすることで、全角から半角に変換できますね。