.NET 50音検索

50音検索って結構面倒ですね・・・

データテーブルに半角カナで保存されているフィールドを50音検索するサンプルです。

SQLのWhere句に Tool.KanaSearchFilterStringメソッドの戻り値を付ければ
データテーブル以外にもデータベースに対しても50音検索できます。

Public Class Form1


    Private _tbl As DataTable

    Public Sub New()

        ' この呼び出しは、Windows フォーム デザイナで必要です。
        InitializeComponent()

        ' InitializeComponent() 呼び出しの後で初期化を追加します。

        'テストデータ作成
        Call CreateTestData()
    End Sub

    'テストデータ作成
    Private Sub CreateTestData()

        Dim tbl As New DataTable
        tbl.Columns.Add("Kana", GetType(String))
        tbl.Rows.Add(New Object() {"ｱ"})
        tbl.Rows.Add(New Object() {"ｶ"})
        tbl.Rows.Add(New Object() {"ｻ"})
        tbl.Rows.Add(New Object() {"ﾀ"})
        tbl.Rows.Add(New Object() {"ﾅ"})
        tbl.Rows.Add(New Object() {"ﾊ"})
        tbl.Rows.Add(New Object() {"ﾏ"})
        tbl.Rows.Add(New Object() {"ﾔ"})
        tbl.Rows.Add(New Object() {"ﾗ"})
        tbl.Rows.Add(New Object() {"ﾜ"})

        Me._tbl = tbl
    End Sub

    Private Sub rdoKana_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles rdoKana_All.CheckedChanged _
            , rdoKana_A.CheckedChanged _
            , rdoKana_Ka.CheckedChanged _
            , rdoKana_Sa.CheckedChanged _
            , rdoKana_Ta.CheckedChanged _
            , rdoKana_Na.CheckedChanged _
            , rdoKana_Ha.CheckedChanged _
            , rdoKana_Ma.CheckedChanged _
            , rdoKana_Ya.CheckedChanged _
            , rdoKana_Ra.CheckedChanged _
            , rdoKana_Wa.CheckedChanged _
            , rdoKana_Other.CheckedChanged

        If DirectCast(sender, RadioButton).Checked = False Then
            Return
        End If

        Dim kana As Tool.KanaSearchType
        If rdoKana_A.Checked Then
            kana = Tool.KanaSearchType.A
        ElseIf rdoKana_Ka.Checked Then
            kana = Tool.KanaSearchType.Ka
        ElseIf rdoKana_Sa.Checked Then
            kana = Tool.KanaSearchType.Sa
        ElseIf rdoKana_Ta.Checked Then
            kana = Tool.KanaSearchType.Ta
        ElseIf rdoKana_Na.Checked Then
            kana = Tool.KanaSearchType.Na
        ElseIf rdoKana_Ha.Checked Then
            kana = Tool.KanaSearchType.Ha
        ElseIf rdoKana_Ma.Checked Then
            kana = Tool.KanaSearchType.Ma
        ElseIf rdoKana_Ya.Checked Then
            kana = Tool.KanaSearchType.Ya
        ElseIf rdoKana_Ra.Checked Then
            kana = Tool.KanaSearchType.Ra
        ElseIf rdoKana_Wa.Checked Then
            kana = Tool.KanaSearchType.Wa
        ElseIf rdoKana_Other.Checked Then
            kana = Tool.KanaSearchType.Other
        End If

        Dim sFilter As String = String.Empty
        sFilter = Tool.KanaSearchFilterString("Kana", kana, Tool.KanaSearchDataType.DataTable)
        Dim row() As DataRow = Me._tbl.Select(sFilter)

        Console.WriteLine("【{0}】", kana.ToString)
        For Each r As DataRow In row
            Console.WriteLine(r.Item(0))
        Next
    End Sub

End Class

Public Class Tool

    ''' <summary>
    ''' 50音検索の検索文字
    ''' </summary>
    ''' <remarks></remarks>
    Public Enum KanaSearchType
        A = 1
        Ka = 2
        Sa = 3
        Ta = 4
        Na = 5
        Ha = 6
        Ma = 7
        Ya = 8
        Ra = 9
        Wa = 10
        Other = 11
    End Enum

    ''' <summary>
    ''' 50音検索するデータ種
    ''' </summary>
    ''' <remarks></remarks>
    Public Enum KanaSearchDataType
        Oracle = 1
        SQLServer = 2
        Access = 3
        DataTable = 4
    End Enum

    ''' <summary>
    ''' 50音検索を行うフィルター文字列を作成します。
    ''' </summary>
    ''' <param name="sFieldNm">50音検索を行うフィールド名</param>
    ''' <param name="kana">検索するカナ</param>
    ''' <param name="dataType">検索するデータ種</param>
    ''' <returns></returns>
    ''' <remarks>
    ''' </remarks>
    Public Shared Function KanaSearchFilterString(ByVal sFieldNm As String, ByVal kana As KanaSearchType, ByVal dataType As KanaSearchDataType) As String

        'Oracle SubStr
        'DataTable SubString
        'SQLServer SubString
        'Access Mid
        Dim funcNm As String
        Select Case dataType
            Case KanaSearchDataType.Oracle
                funcNm = "Substr"
            Case KanaSearchDataType.Access
                funcNm = "Mid"
            Case Else
                funcNm = "SubString"
        End Select


        Dim sFilter As String = String.Empty
        If kana = KanaSearchType.A Then
            sFilter = "{0}({1},1,1) in ('ｱ','ｲ','ｳ','ｴ','ｵ','ｧ','ｨ','ｩ','ｪ','ｫ')"
        ElseIf kana = KanaSearchType.Ka Then
            sFilter = "{0}({1},1,1) in ('ｶ','ｷ','ｸ','ｹ','ｺ')"
        ElseIf kana = KanaSearchType.Sa Then
            sFilter = "{0}({1},1,1) in ('ｻ','ｼ','ｽ','ｾ','ｿ')"
        ElseIf kana = KanaSearchType.Ta Then
            sFilter = "{0}({1},1,1) in ('ﾀ','ﾁ','ﾂ','ﾃ','ﾄ')"
        ElseIf kana = KanaSearchType.Na Then
            sFilter = "{0}({1},1,1) in ('ﾅ','ﾆ','ﾇ','ﾈ','ﾉ')"
        ElseIf kana = KanaSearchType.Ha Then
            sFilter = "{0}({1},1,1) in ('ﾊ','ﾋ','ﾌ','ﾍ','ﾎ')"
        ElseIf kana = KanaSearchType.Ma Then
            sFilter = "{0}({1},1,1) in ('ﾏ','ﾐ','ﾑ','ﾒ','ﾓ')"
        ElseIf kana = KanaSearchType.Ya Then
            sFilter = "{0}({1},1,1) in ('ﾔ','ﾕ','ﾖ','ｬ','ｭ','ｮ')"
        ElseIf kana = KanaSearchType.Ra Then
            sFilter = "{0}({1},1,1) in ('ﾗ','ﾘ','ﾙ','ﾚ','ﾛ')"
        ElseIf kana = KanaSearchType.Wa Then
            sFilter = "{0}({1},1,1) in ('ﾜ','ｦ','ﾝ')"
        ElseIf kana = KanaSearchType.Other Then
            sFilter = "{1} IS NULL OR {0}({1},1,1) not in ('ｱ','ｲ','ｳ','ｴ','ｵ','ｧ','ｨ','ｩ','ｪ','ｫ'" & _
                                                        ",'ｶ','ｷ','ｸ','ｹ','ｺ'" & _
                                                        ",'ｻ','ｼ','ｽ','ｾ','ｿ'" & _
                                                        ",'ﾀ','ﾁ','ﾂ','ﾃ','ﾄ'" & _
                                                        ",'ﾅ','ﾆ','ﾇ','ﾈ','ﾉ'" & _
                                                        ",'ﾊ','ﾋ','ﾌ','ﾍ','ﾎ'" & _
                                                        ",'ﾏ','ﾐ','ﾑ','ﾒ','ﾓ'" & _
                                                        ",'ﾔ','ﾕ','ﾖ','ｬ','ｭ','ｮ'" & _
                                                        ",'ﾗ','ﾘ','ﾙ','ﾚ','ﾛ'" & _
                                                        ",'ﾜ','ｦ','ﾝ')"
        End If


        If Not String.IsNullOrEmpty(sFilter) Then
            sFilter = String.Format(sFilter, funcNm, sFieldNm)
        End If
        Return sFilter
    End Function
End Class