CODEWITHSUNDEEP
t-sqlsql-server-2008full-text-search
Dkong
Dkong

Reputation: 2788

Use multiple words in FullText Search input string

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

Upvotes: 29

Views: 44009

Answers (4)

Milad Akbary
Milad Akbary

Reputation: 115

replace % with all space in your text

example : 'Text like this you want to search' ==> update : 'Text%like%this%you%want%to%search'

Code Example:

declare @text as nvarchar(50) = 'test text'
set @text = REPLACE(@text,' ','%')
SELECT TOP (1000) [id]
  ,[value]
  ,[body]
FROM [test].[dbo].[Table_1]
where CONTAINS([body],@text)

if you want use Full-text Search don't forgot to install that on your mssql

Upvotes: 0

Optimum8
Optimum8

Reputation: 83

For those with SQL Server earlier than 2016 you could use the following SQL to format your search query string:

    DECLARE @ContentsSearchTerm VARCHAR(500) = REPLACE(@SearchTerm, '"',' ');           
    SET @ContentsSearchTerm = '"' + REPLACE(@ContentsSearchTerm, ' ', '*" AND "') + '*"';

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*"'

Upvotes: 0

Chris Pickford
Chris Pickford

Reputation: 8991

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:

""

Upvotes: 0

Aaron D
Aaron D

Reputation: 5876

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?

Upvotes: 48

Related Questions