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
