Reputation: 9209
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
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
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
SELECT [example].[dbo].[escapeFTSSearch] ('Punch Cigar')
...which gives me:
"Punch" AND "Cigar"
WHERE CONTAINS(cigars.*, dbo.escapeFTSSearch(@Search) )
The function is simplistic:
Tweak as needed.
Upvotes: 5