在SQL Server 2005全文索引中删除干扰词

在存储索引之前,会删除干扰词。因此,不可能编写一个搜索停用词的查询。如果你真的想启用这种行为,你需要编辑停用词列表。 ( http://msdn.microsoft.com/en-us/library/ms142551.aspx

我也有同样的问题,经过彻底的搜索,我得出的结论是没有好的解决方案。

作为妥协,我正在实施暴力解决方案:

1) 打开C:\程序文件\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\noiseENU.txt并复制其中的所有文本。

2) 粘贴到应用程序中的代码文件中,用“,”替换换行符,得到这样的List初始化器:

public static List<string> _noiseWords = new List<string>{ "about", "1", "after", "2", "all", "also", "3", "an", "4", "and", "5", "another", "6", "any", "7", "are", "8", "as", "9", "at", "0", "be", "$", "because", "been", "before", "being", "between", "both", "but", "by", "came", "can", "come", "could", "did", "do", "does", "each", "else", "for", "from", "get", "got", "has", "had", "he", "have", "her", "here", "him", "himself", "his", "how", "if", "in", "into", "is", "it", "its", "just", "like", "make", "many", "me", "might", "more", "most", "much", "must", "my", "never", "no", "now", "of", "on", "only", "or", "other", "our", "out", "over", "re", "said", "same", "see", "should", "since", "so", "some", "still", "such", "take", "than", "that", "the", "their", "them", "then", "there", "these", "they", "this", "those", "through", "to", "too", "under", "up", "use", "very", "want", "was", "way", "we", "well", "were", "what", "when", "where", "which", "while", "who", "will", "with", "would", "you", "your", "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z" };

3) 在提交搜索字符串之前,将其分解为单词,并删除干扰词中的任何单词,如下所示:

List<string> goodWords = new List<string>();
string[] words = searchString.Split(' ');
foreach (string word in words)
{
   if (!_noiseWords.Contains(word))
      goodWords.Add(word);
}

这不是一个理想的解决方案,但只要干扰词文件不变,就应该有效。多语言支持将使用按语言分类的列表词典。

这是一个工作函数。文件 noiseENU.txt 按原样复制自 \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData .

    Public Function StripNoiseWords(ByVal s As String) As String
        Dim NoiseWords As String = ReadFile("/Standard/Core/Config/noiseENU.txt").Trim
        Dim NoiseWordsRegex As String = Regex.Replace(NoiseWords, "\s+", "|") ' about|after|all|also etc.
        NoiseWordsRegex = String.Format("\s?\b(?:{0})\b\s?", NoiseWordsRegex)
        Dim Result As String = Regex.Replace(s, NoiseWordsRegex, " ", RegexOptions.IgnoreCase) ' replace each noise word with a space
        Result = Regex.Replace(Result, "\s+", " ") ' eliminate any multiple spaces
        Return Result
    End Function

您还可以在进行查询之前删除干扰词。 语言id列表: http://msdn.microsoft.com/en-us/library/ms190303.aspx

Dim queryText Without Noise As String=removeNoiseWords(queryText,ConnectionString,1033)

公共函数删除NoiseWords(ByVal输入文本为字符串, ByVal cnStr作为字符串, ByVal语言ID为整数)为字符串

    Dim r As New System.Text.StringBuilder
    Try
        If inputText.Contains(CChar("""")) Then
            r.Append(inputText)
        Else
            Using cn As New SqlConnection(cnStr)

                Const q As String = "SELECT display_term,special_term FROM sys.dm_fts_parser(@q,@l,0,0)"
                cn.Open()
                Dim cmd As New SqlCommand(q, cn)
                With cmd.Parameters
                    .Add(New SqlParameter("@q", """" & inputText & """"))
                    .Add(New SqlParameter("@l", languageID))
                End With
                Dim dr As SqlDataReader = cmd.ExecuteReader
                While dr.Read
                    If Not (dr.Item("special_term").ToString.Contains("Noise")) Then
                        r.Append(dr.Item("display_term").ToString)
                        r.Append(" ")
                    End If
                End While
            End Using
        End If
    Catch ex As Exception
        ' ...        
    End Try
    Return r.ToString

End Function

类似于我的方法。

虽然我希望使用全文索引来执行词干、速度和多词搜索等功能,但我实际上只对两个表中的几个nvarchar(100)字段进行了索引。每个表很容易保持在50000行以下。

然后,我按照我最初的帖子中的描述,对搜索字符串中的空格进行替换,使CONTAINS能够处理多个单词,并单独截断单词。

看起来工作得很好,但我会密切关注表现。