全文搜索CONTAINS多列和谓词 - AND [英] FullText search with CONTAINS on multiple columns and predicate - AND

问题描述

我做了这样的事情：

  SELECT * FROM dbo.SearchTable 
 WHERE CONTAINS（（co1，col2，col3，col4），'term1 AND term2'）
  

如果term1和term2位于包含中，则只返回 true 同一列。有没有什么办法可以指定所有的列应该包含在一个AND中？

如果不是，我的想法是将所有搜索列放入JSON并将它们合并为一个。这样我可以全文搜索它们，但仍然可以轻松地提取.NET中的各个列。我假设索引器不会有这个问题，并会免除JSON字符和引号。这是正确的吗？

谢谢

编辑 b

考虑到JSON的想法，爬虫还会索引属性名称，因此我必须将{name}，{details}，{long_details}重命名为{x1}，{x2} ，{x3}以确保他们不会被搜索到。希望如果他们太短，他们不会被索引。

EDIT2

我可以根据系统的Stoplist创建一个停止列表并把属性名称放在那里。

解决方案



< （（co1，col2，col3，col4），'term1'）
AND CONTAINS（（co1，col2， col3，col4），'term2'）;


或者，您可以添加一个新的计算列，其中包含全文索引。添加一个这样的列：

  computedCol AS col1 +''+ col2 +''+ col3 +''+ col4 
  

并创建全文索引：

  CREATE FULLTEXT INDEX ON SearchTable（computedCol LANGUAGE 1033）
 KEY INDEX pk_SearchTable_yourPrimaryKeyName 
  

然后你可以这样做：

  SELECT * FROM dbo.SearchTable 
 WHERE CONTAINS（*，' term1 AND term2'）
  

I have a search table with, say, 4 columns of text data to search.

I do something like this:

SELECT * FROM dbo.SearchTable
WHERE CONTAINS((co1, col2, col3, col4), 'term1 AND term2')

It looks like Contains only returns true if term1 and term2 are in the same column. Is there any way to specify that all columns should be included with an AND?

If not, my idea is to JSON all search columns and stick them into one. That way I can full text search them but still easily extract the individual columns in .NET. I'm presuming that the indexer won't have a problem with this and will dispense with the JSON characters and quotes. Is this correct?

Thanks

EDIT

Thinking about the JSON idea, the crawler would also index the property names so I'd have to rename {name}, {details}, {long_details} to something like {x1}, {x2}, {x3} to ensure they'd not be picked in a search. Hopefully if they're so short they wouldn't be indexed anyway.

EDIT2

I can create a Stoplist, based on the system Stoplist and put the property names into that.

解决方案

This should work:

SELECT * FROM dbo.SearchTable
WHERE CONTAINS((co1, col2, col3, col4), 'term1')
AND CONTAINS((co1, col2, col3, col4), 'term2');

Alternatively, you could add a new computed column with a full text index on it. Add a column like this:

computedCol AS col1 + ' ' + col2 + ' ' + col3 + ' ' + col4

And create the full text index:

CREATE FULLTEXT INDEX ON SearchTable (computedCol LANGUAGE 1033)
KEY INDEX pk_SearchTable_yourPrimaryKeyName

Then you can do this:

SELECT * FROM dbo.SearchTable
WHERE CONTAINS(*, 'term1 AND term2')

这篇关于全文搜索CONTAINS多列和谓词 - AND的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！