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

Question

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.

Inwardness

Answer 1

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"'

Woodprint

Cheers this has prevented errors. However, results are only returning if one of those rows contains 'control of'. I thought this worked with Full-Text Search so that it would also pick up 'of control'? – Inwardness

May be - you should try all the 3 options ant compare the results. And pay attention on garbage words - server just strips these words from searching assuming that this is not interesting and not relevant – Woodprint

Cheers again. However @Keywords is a parameter passed from the Web Application, so I don't have control over the text. Would putting a replace on " " to " AND " be an acceptable solution? Whats the common way of doing searches like this? Cheers – Inwardness

A little late for @Curt who, I assume, has long since solved this problem - but the best solution in that scenario would likely to be to do a split-and-concatenate on the original keywords. Eg split the keywords entry into an array, then concatenate the words back with "AND" in between, then with "OR" in between. You can then run all three queries and UNION them with a "quality". Eg results matching the full phrase get priority 1, results matching the AND query get priority 2, and the "OR" results get priority 3. order by the priority and you have a "most relevant first" results set – Graff

SET @Keywords = '"control of"' will NOT find the exact phrase with CONTAINS or CONTAINSTABLE. It WILL match the ORDER of the words in the phrase. In other words, the word "of" could be a thousand characters away from the word "control" or part of another word like "often". I'm not sure why this is. – Borg

Answer 2

I got this error because my full-text query was passed into a stored proc as a parameter, and apparently SQL will simply silently truncate parameters so that they fit their specified size.

Showalter