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?

You have to quote the string in spaces and double quotes: ' "Punch Cigar" ' and remove double quotes within your search string. That's all! See answer from @Martin Smith — Christian4145, Jan 21 '19 at 11:53

OMG Ponies · Accepted Answer · 2010-09-09T00:02:32.247

Why are you searching by all columns in the CIGAR table? Surely some of them do not use a string/text based data type...

After looking at the CONTAINS documentation, I'd look at a function to properly escape the words for the FTS searching:

CREATE FUNCTION [dbo].[escapeFTSSearch] (
  @SearchParameter NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
  -- Declare the return variable here
  DECLARE @result NVARCHAR(MAX)

  SELECT @result = '"'+ REPLACE(REPLACE(@SearchParameter,'"',''), ' ', '" AND "') +'"'

  -- Return the result of the function
  RETURN @result

END

Test:

SELECT [example].[dbo].[escapeFTSSearch] ('Punch Cigar')

...which gives me:

"Punch" AND "Cigar"

Usage:

WHERE CONTAINS(cigars.*, dbo.escapeFTSSearch(@Search) )

Addendum

The function is simplistic:

it assumes you want all words provided
doesn't support fuzzy searching
~~assumes double quotes aren't in the parameter value~~

Tweak as needed.

You probably want to remove the double quotes and replace them with spaces instead of the empty string. See this answer on why that is more consistent with how SQL treats double quotes: http://stackoverflow.com/a/1233862/399704 — Aaron D, Dec 31 '13 at 17:05
This is not a good answer! See my comment on the question. Just use spaces and double-quotes around your search string. — Christian4145, Jan 21 '19 at 11:55

score 1 · Answer 2 · edited Jan 21 '19 at 12:50

You need to ensure you have leading and trailing double quotes ans spaces. 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)

SQL Server Fulltext Search contains phrase problem

2 Answers2

Test:

Usage:

Addendum