SQL 不能在表或索引视图上使用 CONTAINS 或 FREETEXT 谓词，因为它不是全文索引

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed

提问by DotnetSparrow

I am getting following error in my SQL server 2008 R2 database:

我的 SQL Server 2008 R2 数据库出现以下错误：

Cannot use a CONTAINSor FREETEXTpredicate on table or indexed view 'tblArmy' because it is not full-text indexed.

不能在表或索引视图 'tblArmy' 上使用CONTAINSorFREETEXT谓词，因为它不是全文索引。

回答by Alex Aza

1. Make sure you have full-text search feature installed.

   

2. Create full-text search catalog.

    use AdventureWorks
    create fulltext catalog FullTextCatalog as default
   
    select *
    from sys.fulltext_catalogs
   

3. Create full-text search index.

    create fulltext index on Production.ProductDescription(Description)
    key index PK_ProductDescription_ProductDescriptionID
   

   Before you create the index, make sure:
   - you don't already have full-text search index on the table as only one full-text search index allowed on a table
   - a unique index exists on the table. The index must be based on single-key column, that does not allow NULL.
   - full-text catalog exists. You have to specify full-text catalog name explicitly if there is no default full-text catalog.

1. 确保您安装了全文搜索功能。

   

2. 创建全文搜索目录。

    use AdventureWorks
    create fulltext catalog FullTextCatalog as default
   
    select *
    from sys.fulltext_catalogs
   

3. 创建全文搜索索引。

    create fulltext index on Production.ProductDescription(Description)
    key index PK_ProductDescription_ProductDescriptionID
   

   在创建索引之前，请确保：
   - 表上没有全文搜索索引，因为表上只允许有一个全文搜索索引
   -表上存在唯一索引。索引必须基于单键列，不允许 NULL。
   - 存在全文目录。如果没有默认的全文目录，则必须明确指定全文目录名称。

You can do step 2 and 3 in SQL Sever Management Studio. In object explorer, right click on a table, select Full-Text indexmenu item and then Define Full-Text Index...sub-menu item. Full-Text indexing wizard will guide you through the process. It will also create a full-text search catalog for you if you don't have any yet.

您可以在 SQL Sever Management Studio 中执行第 2 步和第 3 步。在对象资源管理器中，右键单击表格，选择Full-Text index菜单项，然后选择Define Full-Text Index...子菜单项。全文索引向导将指导您完成整个过程。如果您还没有全文搜索目录，它还会为您创建一个全文搜索目录。

You can find more info at MSDN

您可以在MSDN 上找到更多信息

回答by Mohammad Sepahvand

A workaround for CONTAINS: If you don't want to create a full text Index on the column, and performance is not one of your priorities you could use the LIKEstatement which doesn't need any prior configuration:

解决方法CONTAINS：如果您不想在列上创建全文索引，并且性能不是您的优先事项之一，您可以使用LIKE不需要任何事先配置的语句：

Example: find all Products that contains the letter Q:

示例：查找包含字母 Q 的所有产品：

SELECT ID, ProductName
FROM [ProductsDB].[dbo].[Products]
WHERE [ProductsDB].[dbo].[Products].ProductName LIKE '%Q%'

回答by Ashraf Abusada

You must define Full-Text-Indexon all tables in database where you require to use a query with CONTAINSwhich will take sometime.

您必须Full-Text-Index在需要使用查询的数据库中的所有表上定义CONTAINS需要一些时间。

Instead you can use the LIKEwhich will give you instant results without the need to adjust any settings for the tables.

相反，您可以使用 ，LIKE这将为您提供即时结果，而无需调整表格的任何设置。

Example:

例子：

SELECT * FROM ChartOfAccounts WHERE AccountName LIKE '%Tax%'

The same result obtained with CONTAINScan be obtained with LIKE.

用得到相同的结果CONTAINS可以与获得LIKE。

see the result:

看结果：

回答by mellamokb

You might need to enable the table for full-text indexing.

您可能需要为全文索引启用该表。

回答by SimonQuest

you have to add fulltext index on specific fields you want to search.

您必须在要搜索的特定字段上添加全文索引。

ALTER TABLE news ADD FULLTEXT(headline, story);

where "news" is your table and "headline, story" fields you wont to enable for fulltext search

其中“新闻”是您的表格和“标题，故事”字段，您不会启用全文搜索

回答by Icet

There is one more solution to set column Full text to true.

还有一种解决方案可以将列 Full text 设置为 true。

These solution for example didn't work for me

例如，这些解决方案对我不起作用

ALTER TABLE news ADD FULLTEXT(headline, story);

My solution.

我的解决方案。

1. Right click on table
2. Design
3. Right Click on column which you want to edit
4. Full text index
5. Add
6. Close
7. Refresh

1. 右键单击表格
2. 设计
3. 右键单击要编辑的列
4. 全文索引
5. 添加
6. 关闭
7. 刷新

NEXT STEPS

下一步

1. Right click on table
2. Design
3. Click on column which you want to edit
4. On bottom of mssql you there will be tab "Column properties"
5. Full-text Specification -> (Is Full-text Indexed) set to true.

1. 右键单击表格
2. 设计
3. 单击要编辑的列
4. 在 mssql 的底部，您将有“列属性”选项卡
5. 全文规范 ->（是否全文索引）设置为 true。

Refresh

刷新

Version of mssql 2014

mssql 2014 版本

回答by Dnyanesh Mijagiri

Select * from table
where CONTAINS([Column], '"A00*"')  

will act as % same as

将作为 % 与

where [Column] Like 'A00%'