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
– Diado, Commented Jan 6, 2020 at 9:58
See this stackoverflow.com/questions/16507239/…. You'll have to split the input and do a JOIN operation. — Karel Frajták
– Karel Frajták, Commented Jan 6, 2020 at 9: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
– Robbie Dee, Commented Jan 6, 2020 at 9:59
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
– Thom A ♦, Commented Jan 6, 2020 at 10:04

Greg Low · Accepted Answer · 2020-01-06 12:27:22Z

2

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;

answered Jan 6, 2020 at 12:27

Greg Low

1,5961 gold badge7 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

smoore4 · Accepted Answer · 2023-07-28 17:04:49Z

0

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)

answered Jul 28, 2023 at 17:04

smoore4

4,9263 gold badges43 silver badges64 bronze badges

Collectives™ on Stack Overflow

Using SQL CONTAINS with multiple words inside a variable

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related