Use multiple words in FullText Search input string
You will have to do some pre-processing on your @Keyword parameter before passing it into the SQL statement. SQL expects that keyword searches will be separated by boolean logic or surrounded in quotes. So, if you are searching for the phrase, it will have to be in quotes:
SET @Keyword = '"this is a search item"'
If you want to search for all the words then you'll need something like
SET @Keyword = '"this" AND "is" AND "a" AND "search" AND "item"'
For more information, see the T-SQL CONTAINS syntax, looking in particular at the Examples section.
As an additional note, be sure to replace the double-quote character (with a space) so you don't mess up your full-text query. See this question for details on how to do that: SQL Server Full Text Search Escape Characters?
Related videos on Youtube
Dkong
Updated on July 09, 2022Comments
-
Dkong almost 2 years
I have basic stored procedure that performs a full text search against 3 columns in a table by passing in a
@Keyword
parameter. It works fine with one word but falls over when I try pass in more than one word. I'm not sure why. The error says:Syntax error near 'search item' in the full-text search condition 'this is a search item'
SELECT S.[SeriesID], S.[Name] as 'SeriesName', P.[PackageID], P.[Name] FROM [Series] S INNER JOIN [PackageSeries] PS ON S.[SeriesID] = PS.[PackageID] INNER JOIN [Package] P ON PS.[PackageID] = P.[PackageID] WHERE CONTAINS ((S.[Name],S.[Description], S.[Keywords]),@Keywords) AND (S.[IsActive] = 1) AND (P.[IsActive] = 1) ORDER BY [Name] ASC
-
MikeTeeVee over 11 yearsThank you. Not sure why the MSDN documentation I looked at didn't have this simple, yet extremely common, example.
-
Raj over 10 years@Aaron Is this AND Operator work if use MATCH instead of CONTAINS ? Because I am not getting any output for AND with MATCH condition
-
MonsterMMORPG over 7 yearsHow do we search for phrases that contains " ? do we put double "" or what is the syntax?