Syntax error near 'of' in the full-text search condition 'control of'

sql-server-2008 full-text-search contains
Curt picture Curt · Feb 24, 2012 · Viewed 15k times · Source

I have the following WHERE clause:

WHERE (@Keywords IS NULL
            OR (CONTAINS((p.Title, p.Area, p.[Message]), @Keywords))
        )

If @Keywords = 'control', then the query executes successfully and filters my records

If @Keywords = 'control of', then I get the following error:

Syntax error near 'of' in the full-text search condition 'control of'.

Why is this and what can I do to resolve the issue?

The main reason I'm using this method over using LIKE condition is so that I can search multiple words.

Answer

Oleg Dok picture Oleg Dok · Feb 24, 2012

Enclose the keywords in double quotes if you want to search exactly for control of

SET @Keywords = '"control of"'

If you want to search for control and of, use the following:

SET @Keywords = 'control AND of'

But server may consider of as a garbage or stop word, so in this case use the following:

SET @Keywords = 'control AND "of"'

Related questions

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 indexed view 'tblArmy' because it is not full-text indexed.

Read More
SQL Fuzzy Matching

Hope i am not repeating this question. I did some search here and google before posting here. I am running a eStore with SQL Server 2008R2 with Full Text enabled. My requirements, There is a Product Table, which has product …

Read More
Use multiple words in FullText Search input string

I have basic stored procedure that performs a full text search against 3 columns in a table by passing in a @Keyword parameter. It works fine with one word but falls over when I try pass in more than one word. …

Read More