Use multiple words in FullText Search input string

user_112603 0
Viewed 40397

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
full-text-search sql-server-2008 tsql
2 Answers

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:

""
user_28355410
Related