如何在全文索引搜索SQL的两列上执行CONTAINS（） [英] How to do a CONTAINS() on two columns of Full Text Index Search SQL

问题描述

我有一个包含以下列的表（MyTable）：

Col1：NameID VARCHAR（50）PRIMARY KEY NOT NULL
Col2：Address VARCHAR（ 255）

数据示例：

名称：'1 24'
地址：'1234 Main St。'

我在使用默认参数编制目录之后在表格上做了全文索引。

如何实现以下查询：

  SELECT * FROM MyTable 
 WHERE CONTAINS（NameID，'1'） 
 AND CONTAINS（地址，'Main St.'）; 
  

但是我的查询没有返回任何结果，这是没有意义的，因为这确实有效： p>

  SELECT * FROM MyTable 
 WHERE CONTAINS（Address，'Main St.'）; 
  

等等：

<$ p $
和NameID LIKE'1％'

但这也行不通：

  SELECT * FROM MyTable 
 WHERE CONTAINS（NameID，'1'）; 
  

当我选择此列时，为何无法在索引的主键列（Name）上进行查询在设置全文索引时要包含在
地址栏中？

感谢您！

解决方案

由于NameID字段的类型是varchar，所以全文将处理索引。

CONTAINS（NameID，'1'）不返回任何搜索结果的原因是'1'（以及其他这样的小数字）被全部视为噪音词并在索引时间过滤掉。

要获取停用词的列表，请运行以下查询 -

  select * from sys.fulltext_system_stopwords where language_id = 1033; 
  

您需要关闭或修改停止列表，其中的一个示例可以找到这里。

I have a table (MyTable) with the following columns:

Col1: NameID VARCHAR(50) PRIMARY KEY NOT NULL Col2: Address VARCHAR(255)

Data Example:

Name: '1 24' Address: '1234 Main St.'

and i did a full text index on the table after making the catalog using default params.

How can I achieve the following query:

 SELECT * FROM MyTable
 WHERE CONTAINS(NameID, '1')
 AND CONTAINS(Address, 'Main St.');

But my query is returning no results, which doesn't make sense because this does work:

 SELECT * FROM MyTable
WHERE CONTAINS(Address, 'Main St.');

and so does this:

 SELECT * FROM MyTable
 WHERE CONTAINS(Address, 'Main St.')
 AND NameID LIKE '1%'

but this also doesn't work:

 SELECT * FROM MyTable
 WHERE CONTAINS(NameID, '1');

Why can't I query on the indexed, primary key column (Name) when I selected this column to be included with the Address column when setting up the Full Text Index?

Thanks in advance!

解决方案

Since the NameID field is of type varchar, full-text will handle the indexing just fine.

The reasoning behind CONTAINS(NameID, '1') not returning any search results is that '1' (and other such small numbers) are regarded as noise words by full-text and filtered out during indexing time.

To get a list of the stop words, run the following query -

select * from sys.fulltext_system_stopwords where language_id = 1033;

You need to turn off or modify the stop list, an example of which can be found here.

这篇关于如何在全文索引搜索SQL的两列上执行CONTAINS（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！