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

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

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

1 アセンブリ（dll）の作成
2 SQL CLR関数の作成
- 2.1 アセンブリ（dll）の登録
- 2.2 CLR関数の作成
3 CLR関数の実行

アセンブリ（dll）の作成

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

プロジェクトの作成

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

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

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

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

関数（メソッド）の実装

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

C#での実装

// Microsoft.VisualBasicをusing
using Microsoft.VisualBasic;
// Microsoft.SqlServer.Serverをusing
using Microsoft.SqlServer.Server;

namespace SqlClr
{
    public class Padding
    {
        // 日本語を表すロケールID
        private const int JapaneseLocaleID = 1041;

        // 半角を全角に変換するメソッド
        [SqlFunction]
        public static string StrConvToWide(string str)
        {
            return Strings.StrConv(str, VbStrConv.Wide, JapaneseLocaleID);
        }

        // 全角を半角に変換するメソッド
        [SqlFunction]
        public static string StrConvToNarrow(string str)
        {
            return Strings.StrConv(str, VbStrConv.Narrow, JapaneseLocaleID);
        }

        // カタカナをひらがなに変換するメソッド
        [SqlFunction]
        public static string StrConvToHiragana(string str)
        {
            return Strings.StrConv(str, VbStrConv.Hiragana, JapaneseLocaleID);
        }

        // ひらがなをカタカナに変換するメソッド
        [SqlFunction]
        public static string StrConvToKatakana(string str)
        {
            return Strings.StrConv(str, VbStrConv.Katakana, JapaneseLocaleID);
        }
    }
}

// Microsoft.VisualBasicをusing

using Microsoft.VisualBasic;

// Microsoft.SqlServer.Serverをusing

using Microsoft.SqlServer.Server;

namespace SqlClr

{

public class Padding

{

// 日本語を表すロケールID

private const int JapaneseLocaleID = 1041;

// 半角を全角に変換するメソッド

[SqlFunction]

public static string StrConvToWide(string str)

{

return Strings.StrConv(str, VbStrConv.Wide, JapaneseLocaleID);

}

// 全角を半角に変換するメソッド

[SqlFunction]

public static string StrConvToNarrow(string str)

{

return Strings.StrConv(str, VbStrConv.Narrow, JapaneseLocaleID);

}

// カタカナをひらがなに変換するメソッド

[SqlFunction]

public static string StrConvToHiragana(string str)

{

return Strings.StrConv(str, VbStrConv.Hiragana, JapaneseLocaleID);

}

// ひらがなをカタカナに変換するメソッド

[SqlFunction]

public static string StrConvToKatakana(string str)

{

return Strings.StrConv(str, VbStrConv.Katakana, JapaneseLocaleID);

}

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

Visual Basicでの実装

' Microsoft.SqlServer.ServerをImports
Imports Microsoft.SqlServer.Server

Public Class Padding
    ' 日本語を表すロケールID
    Private Const JapaneseLocaleID As Integer = 1041

    ' 半角を全角に変換するメソッド
    <SqlFunction()>
    Public Shared Function StrConvToWide(str As String) As String
        Return StrConv(str, VbStrConv.Wide, JapaneseLocaleID)
    End Function

    ' 全角を半角に変換するメソッド
    <SqlFunction()>
    Public Shared Function StrConvToNarrow(str As String) As String
        Return StrConv(str, VbStrConv.Narrow, JapaneseLocaleID)
    End Function

    ' カタカナをひらがなに変換するメソッド
    <SqlFunction()>
    Public Shared Function StrConvToHiragana(str As String) As String
        Return StrConv(str, VbStrConv.Hiragana, JapaneseLocaleID)
    End Function

    ' ひらがなをカタカナに変換するメソッド
    <SqlFunction()>
    Public Shared Function StrConvToKatakana(str As String) As String
        Return StrConv(str, VbStrConv.Katakana, JapaneseLocaleID)
    End Function
End Class

' Microsoft.SqlServer.ServerをImports

Imports Microsoft.SqlServer.Server

Public Class Padding

' 日本語を表すロケールID

Private Const JapaneseLocaleID As Integer = 1041

' 半角を全角に変換するメソッド

<SqlFunction()>

Public Shared Function StrConvToWide(str As String) As String

Return StrConv(str, VbStrConv.Wide, JapaneseLocaleID)

End Function

' 全角を半角に変換するメソッド

<SqlFunction()>

Public Shared Function StrConvToNarrow(str As String) As String

Return StrConv(str, VbStrConv.Narrow, JapaneseLocaleID)

End Function

' カタカナをひらがなに変換するメソッド

<SqlFunction()>

Public Shared Function StrConvToHiragana(str As String) As String

Return StrConv(str, VbStrConv.Hiragana, JapaneseLocaleID)

End Function

' ひらがなをカタカナに変換するメソッド

<SqlFunction()>

Public Shared Function StrConvToKatakana(str As String) As String

Return StrConv(str, VbStrConv.Katakana, JapaneseLocaleID)

End Function

End Class

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

アセンブリ（dll）の作成

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

SQL CLR関数の作成

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

アセンブリ（dll）の登録

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

-- アセンブリを登録するデータベースのTRUSTWORTHYプロパティをON
ALTER DATABASE TestDatabase SET TRUSTWORTHY ON;
-- アセンブリの登録
CREATE ASSEMBLY SqlClrStrConv
FROM 'C:\example\SqlClrStrConv.dll'
WITH PERMISSION_SET = SAFE;

-- アセンブリを登録するデータベースのTRUSTWORTHYプロパティをON

ALTER DATABASE TestDatabase SET TRUSTWORTHY ON;

-- アセンブリの登録

CREATE ASSEMBLY SqlClrStrConv

FROM 'C:\example\SqlClrStrConv.dll'

WITH PERMISSION_SET = SAFE;

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

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

CLR関数の作成

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

-- 半角を全角に変換する関数
CREATE FUNCTION StrConvToWide(
    @string NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME SqlClrStrConv.[SqlClr.Padding].StrConvToWide;
GO

-- 全角を半角に変換する関数
CREATE FUNCTION StrConvToNarrow(
    @string NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME SqlClrStrConv.[SqlClr.Padding].StrConvToNarrow;
GO

-- カタカナをひらがなに変換する関数
CREATE FUNCTION StrConvToHiragana(
    @string NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME SqlClrStrConv.[SqlClr.Padding].StrConvToHiragana;
GO

-- ひらがなをカタカナに変換する関数
CREATE FUNCTION StrConvToKatakana(
    @string NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME SqlClrStrConv.[SqlClr.Padding].StrConvToKatakana;
GO

-- 半角を全角に変換する関数

CREATE FUNCTION StrConvToWide(

@string NVARCHAR(MAX)

)

RETURNS NVARCHAR(MAX)

AS EXTERNAL NAME SqlClrStrConv.[SqlClr.Padding].StrConvToWide;

-- 全角を半角に変換する関数

CREATE FUNCTION StrConvToNarrow(

@string NVARCHAR(MAX)

)

RETURNS NVARCHAR(MAX)

AS EXTERNAL NAME SqlClrStrConv.[SqlClr.Padding].StrConvToNarrow;

-- カタカナをひらがなに変換する関数

CREATE FUNCTION StrConvToHiragana(

@string NVARCHAR(MAX)

)

RETURNS NVARCHAR(MAX)

AS EXTERNAL NAME SqlClrStrConv.[SqlClr.Padding].StrConvToHiragana;

-- ひらがなをカタカナに変換する関数

CREATE FUNCTION StrConvToKatakana(

@string NVARCHAR(MAX)

)

RETURNS NVARCHAR(MAX)

AS EXTERNAL NAME SqlClrStrConv.[SqlClr.Padding].StrConvToKatakana;

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

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

CLR関数の実行

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

select
    -- 半角を全角に変換する
    dbo.StrConvToWide('abcde'),
    -- 全角を半角に変換する
    dbo.StrConvToNarrow('ｖｗｘｙｚ'),
    -- カタカナをひらがなに変換する
    dbo.StrConvToHiragana('アイウエオ'),
    -- ひらがなをカタカナに変換する
    dbo.StrConvToKatakana('かきくけこ');