SQL Server Fulltext Search contains phrase problem

Question

I have a problem with SQL Server 2008 full text search I have the following query

SELECT *
FROM cigars
WHERE CONTAINS(cigars.*, @Search )

@Search is a value that is passed in through a stored procedure. But, thats not the problem.

What is is if someone searches for say 'Punch' it works fine and I get all cigars that match.

Where the problem lays is if they search for 'Punch Cigar' I get this error.

Syntax error near 'Cigar' in the full-text search condition 'Punch Cigar'.

Any idea on how I can get it to allow for it to search for that phrase?

Answer 1

You need to ensure you have leading and trailing double quotes. i.e. the value of @Search should be "Punch Cigar"

Further to OMG's comments about escaping you would definitely need to strip out any embedded double quotes.

declare @Search varchar(1000)
set @Search = 'punch and" cigar'

set @Search = '"' + REPLACE(@Search,'"','') + '"'

select * from sys.dm_fts_parser(@Search,1033,null,0)