Full-Text Search Not working (FREETEXT - CONTAINS)

Question

Freetext not return all of the words from table. And Contains not work

I have a one row wich include in mycolumn="Life of a King"

I tried 2 method; First "contains"

SELECT * FROM MYTABLE WHERE CONTAINS(MYCOLUMN,'Life NEAR of NEAR a NEAR King')

It returns NOTHING

Second:

SELECT * FROM MYTABLE WHERE FREETEXT(MYCOLUMN,'Life of a King')

It returns 237 rows! which is ;

"Life of Pie","It's a Wonderfull Life","The Lion King","King Arthur","Life Story","Life of a King" etc...

I want to return row which only include "Life"+"of"+"a"+"King" words together.

Thanks for replies!

have you tried SELECT * FROM MYTABLE WHERE MYCOLUMN LIKE '%Life of a King%' ? — skarist, Jul 14 '14 at 15:24

score 0 · Accepted Answer · answered Jul 15 '14 at 00:30

I am assuming full text field is nvarchar.

Here is my example:

CREATE TABLE [dbo].[FullTextTable](
    [ID] [int] NOT NULL PRIMARY KEY,
    [FullTextField] [nvarchar](max) NOT NULL
    );
GO

CREATE FULLTEXT INDEX ON FullTextTable([FullTextField]) 
   KEY INDEX [PK_FullTextTable]
   WITH STOPLIST = SYSTEM;
GO

Following query returning exact value:

SELECT FullTextField
FROM FullTextTable
WHERE
CONTAINS 
(FullTextField, N'"Life NEAR of NEAR a NEAR King"' );
GO

score 0 · Answer 2 · edited Feb 02 '21 at 07:27

You must consider below points

The column for which you are doing searching should have FULLTEXT INDEX
Check the searching term is exists in the table

SELECT * FROM sys.dm_fts_index_keywords(DB_ID('your_DB_Name'), OBJECT_ID('_your_table_Name')) where display_term like '%your_searching_keyword%'
The "Change Tracking" property should be set to "automatic". If after creating index you are going to add or delete rows from the table or data in the table is not static.

Full-Text Search Not working (FREETEXT - CONTAINS)

2 Answers2