CODEWITHSUNDEEP
sqlsql-servert-sqlfull-text-search
dswatik
dswatik

Reputation: 9209

SQL Server Fulltext Search contains phrase problem

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?

Upvotes: 3

Views: 8924

Answers (2)

Martin Smith
Martin Smith

Reputation: 453887

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)

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332731

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.

Upvotes: 5

Related Questions