Using SQL CONTAINS with multiple words inside a variable

Question

I'm trying to use SQL CONTAINS function with variables. Usually, I can use CONTAINS the following way:

WHERE CONTAINS(tablename, '"some text to search for"')

However, I can't figure out how to insert a variable inside that function that would allow me to search with multiple words.

WHERE CONTAINS(tablename, @Keyword)

only allows to search for one word and will throw an exception if multiple words are passed to it.

Msg 7630, Level 15, State 3, Procedure dbo.bp_SearchCategoryByKeyword, Line 11 [Batch Start Line 0] Syntax error near 'this' in the full-text search condition

"_will throw an exception if multiple words are passed to it_" What's the exception? — Diado, Jan 06 '20 at 09:58
See this https://stackoverflow.com/questions/16507239/join-comma-delimited-data-column. You'll have to split the input and do a JOIN operation. — Karel Frajták, Jan 06 '20 at 09:58
Msg 7630, Level 15, State 3, Procedure dbo.bp_SearchCategoryByKeyword, Line 11 [Batch Start Line 0] Syntax error near 'this' in the full-text search condition 'Check this'. — Robbie Dee, Jan 06 '20 at 09:59
SQL SMS? SQL Server can't send texts. What version of SQL Server are you using? E.g. SQL Server 2014 Standard Edition. — Thom A, Jan 06 '20 at 10:02
The accepted answer there uses a `WHILE`, @KarelFrajták . There are *far* better options than that for splitting strings, even in (the completely unsupported) SQL Server 2008. — Thom A, Jan 06 '20 at 10:04
If there are multiple words, i.e. "some text", do you want results that contain the 2 words "some text" next to each other, or results where the words appear anywhere (i.e. "text blah blah blah some")? — Keith, Jan 28 '20 at 16:17
Are you sure `@Keyword` has quotes around it (in the string itself)? SQL Server is kind of picky about the syntax. You can use [SoftCircuits.FullTextSearchQuery](https://github.com/SoftCircuits/FullTextSearchQuery) to convert a query to the right syntax for SQL Server full-text search. — Jonathan Wood, Mar 03 '20 at 15:34

score 2 · Answer 1 · answered Jan 06 '20 at 12:27

I just tried this and it worked as expected:

DECLARE @SearchTerms varchar(8000) = 'file AND read';

SELECT MessageID, Description 
FROM dbo.Messages
WHERE CONTAINS(Description, @SearchTerms)
ORDER BY MessageID;

This also worked:

DECLARE @SearchTerms varchar(8000) = '"file" AND "read"';

SELECT MessageID,Description 
FROM dbo.Messages
WHERE CONTAINS(Description, @SearchTerms)
ORDER BY MessageID;

score 0 · Answer 2 · answered Jul 28 '23 at 17:04

It is a bit of a hack, but creating a second variable from the first, with double quotes around the first var, will work:

DECLARE @p_searchtermTitle NVARCHAR(255) = '""'
DECLARE @p_searchtermTitle2 NVARCHAR(255) = '""'

SET @p_searchtermTitle = 'The Birth of the World'
SET @p_searchtermTitle2 = '"' + @p_searchtermTitle + '"'

SELECT @p_searchtermTitle, @p_searchtermTitle2, OT.Title
FROM ObjTitles OT 
WHERE CONTAINS(OT.Title, @p_searchtermTitle2)

Using SQL CONTAINS with multiple words inside a variable

2 Answers2