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!

Answer 1

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