在全文搜索输入字符串中使用多个单词 [英] Use multiple words in FullText Search input string

问题描述

我有一个基本的存储过程，它通过传入 @Keyword 参数来对表中的3列执行全文搜索。用一个单词可以正常工作，但当我尝试传递多个单词时会下降。我不知道为什么。错误说：

全文搜索条件'搜索项'附近的语法错误'这是搜索项'

  SELECT S. [SeriesID]，
 S. [Name] as'SeriesName'，
 P. [PackageID]，
 P 。[Name] 
 FROM [Series] S 
 INNER JOIN [PackageSeries] PS ON S. [SeriesID] = PS。[PackageID] 
 INNER JOIN [Package] P ON PS。[PackageID ] = P。[PackageID] 
 Where Contains（（S. [Name]，S。[Description]，S. [Keywords]），@ Keywords）
 AND（S. [IsActive] = 1 ）AND（P. [IsActive] = 1）
 ORDER BY [Name] ASC 
  

解决方案

在将您的@Keyword参数传递给SQL语句之前，您必须对其进行一些预处理。 SQL期望关键字搜索将由布尔逻辑分隔或用引号括起来。所以，如果你正在搜索这个短语，它必须在引号中：

  SET @Keyword ='这是搜索项目''
  

如果您想搜索所有单词，则需要类似

$ b $ pre $ SET @Keyword ='这个AND是AND一个AND搜索AND项目''


有关更多信息，请参阅 T-SQL CONTAINS语法，具体查看示例 p>

作为附加说明，请务必替换双引号字符（用空格），以免弄乱全文查询。有关如何执行此操作的详细信息，请参阅此问题： SQL Server全文搜索转义字符？

I have basic stored procedure that performs a full text search against 3 columns in a table by passing in a @Keyword parameter. It works fine with one word but falls over when I try pass in more than one word. I'm not sure why. The error says:

Syntax error near 'search item' in the full-text search condition 'this is a search item'

SELECT     S.[SeriesID], 
           S.[Name] as 'SeriesName',
           P.[PackageID],
           P.[Name]     
FROM       [Series] S
INNER JOIN [PackageSeries] PS ON S.[SeriesID] = PS.[PackageID]
INNER JOIN [Package]       P  ON PS.[PackageID] = P.[PackageID]
WHERE CONTAINS ((S.[Name],S.[Description], S.[Keywords]),@Keywords)
AND   (S.[IsActive] = 1) AND (P.[IsActive] = 1) 
ORDER BY [Name] ASC

解决方案

You will have to do some pre-processing on your @Keyword parameter before passing it into the SQL statement. SQL expects that keyword searches will be separated by boolean logic or surrounded in quotes. So, if you are searching for the phrase, it will have to be in quotes:

SET @Keyword = '"this is a search item"'

If you want to search for all the words then you'll need something like

SET @Keyword = '"this" AND "is" AND "a" AND "search" AND "item"'

For more information, see the T-SQL CONTAINS syntax, looking in particular at the Examples section.

As an additional note, be sure to replace the double-quote character (with a space) so you don't mess up your full-text query. See this question for details on how to do that: SQL Server Full Text Search Escape Characters?

这篇关于在全文搜索输入字符串中使用多个单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！