CONTAINS句では変な単語区切り(?)が入ってしまい思ったような検索が出来ない。
同等の機能を持つようなStoredProcedureを作成する。

  • sp_contains(column_name, keywords)のような形式
  • keywordsはスペース区切りの文字列。
  • column_name like ‘%keyword1%’ or column_name like ‘%keyword2%’…のような形に展開。
  • nullの場合はtrueを返す。
  • keywordsの解釈は行ごとに行うのではなく、クエリー実行時に一度だけ行うのが望ましい。
    (パフォーマンスの問題)
  • 1つのクエリーに複数のsp_containsを使用することもある。
  • 複数のcolumnに対しても同様の処理ができるとなお良い。

  • StoredProcedureでは配列が扱えず、Tableで代替すると遅い。
  • 結果、CLRで対応する事になった。
Partial Public Class UserDefinedFunctions

  Public Shared Function KeywordsMatch(ByVal aTargetData As String, ByVal aKeywordNotSpilitted As String) As Boolean
    If (aKeywordNotSpilitted Is Nothing) Or (aKeywordNotSpilitted Is “”) Then
      Return True
    End If

    Dim tKeywords As String() = aKeywordNotSpilitted.Split(” “c)
    For Each tKeyword As String In tKeywords
      If aTargetData.Contains(tKeyword) Then
        Return True
      End If
    Next
    Return False
  End Function
End Class
  • 使用時の注意点として、DataSourceのParameterにnull(nothing)を許容するために、CancelSelectOnNullParameterの値をFalseに設定する必要がある。
  • キーワードの分割を毎レコードごとにやっているので、多少の高速化は可能。(今回のプロジェクトでは問題にならない速度だったので、調査していない。)

3件のコメント

koreyasu · 2009-04-07 17:10

文字列をSplitして返すStoredProcedure
http://code.nanigac.com/source/view/515

koreyasu · 2009-04-08 16:28

SQLServer2008だと配列っぽいものを受け渡し可能なようで。
http://blogs.wankuma.com/mura/archive/2008/01/08/116485.aspx

iwamoto · 2009-04-08 21:06

ストアドプロシージャでは速度がでなかったため、SQL CLRを使いました。
SQL CLRを使って以下のようなコードを書けば、Contains句のようなことができます。
ただし、
・keywordsの解釈は行ごとに行っている。
(速度が問題になったときに実装しようと思います。static変数を使ってキャッシュ使用と思っているので、SQL CLRのセキュリティ権限を変更しなければなりません。)
・複数のカラムに対して同様の処理はできない。

Partial Public Class UserDefinedFunctions
_
Public Shared Function KeywordsMatch(ByVal aTargetData As String, ByVal aKeywordNotSpilitted As String) As Boolean
‘ コードをここに追加してください
If (aKeywordNotSpilitted Is Nothing) Or (aKeywordNotSpilitted Is “”) Then
Return True
End If

Dim tKeywords As String() = aKeywordNotSpilitted.Split(” “c)
For Each tKeyword As String In tKeywords
If aTargetData.Contains(tKeyword) Then
Return True
End If
Next
Return False
End Function
End Class

コメントを残す

メールアドレスが公開されることはありません。