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

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

Cannot use a CONTAINS or FREETEXT predicate on table or inde开发者_如何学运维xed view 'tblArmy' because it is not full-text indexed.

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

2. Create full-text search catalog (if needed)

   First check if any catalog already exists

     select *
     from sys.fulltext_catalogs
   

   If no catalog is found create one

     use [DatabaseName]
     create fulltext catalog FullTextCatalog as default
   

   you can verify that the catalog was created in the same way as above

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.

You can do step 2 and 3 in SQL Sever Management Studio. In object explorer, right click on a table, select Full-Text index menu 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.

You can find more info at MSDN

After following the steps you need a few minutes so that the full text search index is created (this depends on the size of the table and column data)

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 LIKE statement which doesn't need any prior configuration:

Example: find all Products that contains the letter Q:

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

You must define Full-Text-Index on all tables in database where you require to use a query with CONTAINS which will take sometime.

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

Example:

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

The same result obtained with CONTAINS can be obtained with LIKE.

see the result:

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

There is one more solution to set column Full text to 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

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.

Refresh

Version of mssql 2014

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

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

will act as % same as

where [Column] Like 'A00%'