Full text search syntax error

Question

I created a full text search with a catalog and index and the contains query works fine when I run a query with one word like below.

SELECT Name
FROM dbo.Gifts
WHERE CONTAINS(Name, 'gift')

it returns 'test gift'

I have only one row in the table and the data in the Name column looks like this: 'test gift'

but when I run the conaints query with this statement:

SELECT Name
FROM dbo.Gifts
WHERE CONTAINS(Name, 'test gift')

It throws an error saying: Syntax error near 'gift' in the full-text search condition 'test gift'.

I thought contains could query phrases and multiple words that match and sound alike?

Answer 1

You need double quotes to manage that space, keeping in mind that you are searching for the entire string, and not the words of the string. The following query would find "test gift" but not "gift test"

SELECT Name
FROM dbo.Gifts
WHERE CONTAINS(Name, '"test gift"')

or, if you want to search words individually, it would be

SELECT Name
FROM dbo.Gifts
WHERE CONTAINS(Name, '"test" AND "gift"')

this second one should get you a field with "gift test" as well as "test gift"