Use multiple words in FullText Search input string

Question

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

score 46 · Accepted Answer · edited May 23 '17 at 12:25

46

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?

edited May 23 '17 at 12:25

Community

1
1

answered Aug 11 '10 at 18:28

Aaron D

5,817
1
36
51

1

Thank you. Not sure why the MSDN documentation I looked at didn't have this simple, yet extremely common, example. – MikeTeeVee Feb 18 '13 at 23:34
@Aaron Is this AND Operator work if use MATCH instead of CONTAINS ? Because I am not getting any output for AND with MATCH condition – Raj Dec 30 '13 at 06:12
How do we search for phrases that contains " ? do we put double "" or what is the syntax? – Furkan Gözükara Mar 22 '17 at 00:03

score 0 · Answer 2 · answered Mar 17 '21 at 17:07

Further to Aaron's answer, provided you are using SQL Server 2016 or greater (130), you could use the in-built string fuctions to pre-process your input string. E.g.

SELECT
    @QueryString = ISNULL(STRING_AGG('"' + value + '*"', ' AND '), '""')
FROM
    STRING_SPLIT(@Keywords, ' ');

Which will produce a query string you can pass to CONTAINS or FREETEXT that looks like this:

'"this*" AND "is*" AND "a*" AND "search*" AND "item*"'

or, when @Keywords is null:

""

Use multiple words in FullText Search input string

2 Answers2

Linked