sameerkattel on Mon, 12 May 2014 04:53:10

I have a table named Documents that has Content field full text indexed


Now i need to create a query in such a way that the query should return docs that have at least one word "hello" that does not have word "world" following it.

   --sth like
   select content from documents
   where contains(Content,N'hello(not followed by word "world")')

And the result should be

  1. "hello" -- since this does not have world following hello in it"

  2. "hello friend and hello world" -- this document has word hello that is not followed by world it it"

Can a full text query be formulated in a way to achieve above results? If so how?? I want to avoid parsing Content manually as much possible as I can.



Sofiya Li on Tue, 13 May 2014 07:05:39

Hi Mokchhya,

According to your description, the issue regards SQL Server Search, I will help you move the question on the related forums, and It is appropriate and more experts will assist you.

In addition, I do a test. If you want to get the results which contains ‘hello’ characters and not contains’ world’ characters. I recommend you use NOT on any boolean expression. For example, if you create full test catalog and full test index. When you need to find the document records who has “hello” in their content, and does not have “ world” in content, CONTAINS( ) is takes one keyword to match with the records.

select * from <database>.<schema>.<table> 

  where  contains (Name, 'hello') and not contains (Name, 'world')

For more information, see:

if you don’t use full text search, you can use Like with wildcarding to scan a column. For example,

;With cte As

(select * from <database>.<schema>.<table> 

Where name like ‘hello%’


select * from cte 

where name not like ‘%world’

Sofiya Li

sameerkattel on Wed, 21 May 2014 04:01:26

Hi Sofia,

  Thanks for moving the post to appropriate forum!!!

I understand the not contains thing.. but what i am asking is a different thing.The word "world" matters if it only occurs after hello.

I think I have clearly put my requirements.