How to use a column name in CONTAINSTABLE for the search condition?

Question

I'm surprised to find that neither CONTAINS or CONTAINSTABLE seems to support syntax like the following where you pass a column name in for the last Search Condition parameter.

SELECT *
FROM dbo.Articles AS a
WHERE EXISTS
(
   SELECT *
   FROM dbo.Terms AS t
   INNER JOIN CONTAINSTABLE(dbo.Articles, (ArticleBody), t.FulltextTerm)
      AS ct ON ct.[Key] = a.ArticleId
)

The above query returns an "Incorrect syntax near 't'" error message.

The Terms table contains multiple rows with a FulltextTerm column, and if any of those FulltextTerm values is in the ArticleBody, it should be a match so that particular Article is selected. This is what I'm trying to achieve.

CONTAINS and CONTAINSTABLE appear to only support string literals or variables for the Search Condition parameter, which is very limiting. If that's the only option, it requires a lot more code and will certainly be much slower if I need to iterate thru the Terms table with a cursor or loop.

Am I missing a trick here, or any workarounds someone can suggest - preferably a set-based solution, i.e. avoiding loops.

Not sure if it works with Full-text but did you try CROSS APPLY instead of INNER JOIN? That's how we do it with table-valued functions, anyway... — Aaron Bertrand, Aug 06 '14 at 21:54
Very good idea, I just tried the CROSS APPLY, however same problem :( This gave me the idea of creating an inline TVF that uses CONTAINSTABLE and I could CROSS APPLY to the TVF, passing in the FulltextTerm (and ArticleId) ... however this too didn't work! The error message is "The inline function "dbo.tvf_CheckArticleForTerm" cannot take correlated parameters or subqueries because it uses a full-text operator." The TVF itself works, but just doesn't work when CROSS APPLY'ing to it. Apparently SQL Server is checking for and disallowing this scenario. — Ben Amada, Aug 06 '14 at 23:54
A multi-statement TVF however does work (same scenario described above, i.e. CONTAINSTABLE in the TVF and CROSS APPLY to that TVF). The only issue with this is it's quite slow ... about 2.5 seconds whereas (for testing) if I manually concatenate all the FulltextTerms into a string literal and use a normal CONTAINSTABLE, it's 5 milliseconds. At least the multi-statement TVF gives me something. Not sure yet, but I may end up turning to using a LIKE statement and abandoning Full Text for this ... after narrowing down the Articles to as small a subset as possible. — Ben Amada, Aug 06 '14 at 23:59
For the record, I ended up with a loop solution that looped thru each `FulltextTerm` before the main query, storing the matching ArticleIds returned via CONTAINSTABLE in a table variable. After all the looping, the main query used the @ArticleIds table variable in an EXISTS clause. Although not as elegant as I would have liked, it performs well (much better than what I was getting with a multi-statement TVF) and allows me to still use the full-text engine which also helps greatly on performance when there are thousands of article bodies to check. — Ben Amada, Aug 17 '14 at 02:06

score 0 · Answer 1 · answered Jul 24 '19 at 15:46

What about merging all your terms in one variable, and then using the CONTAINSTABLE, as below:-

declare @term as table(
    FulltextTerm nvarchar(60)
)

insert into @term values ('light NEAR aluminum')
insert into @term values ('lightweight NEAR aluminum')

Declare @FulltextTerm nvarchar(max)=''
select @FulltextTerm=@FulltextTerm+' OR ('+FulltextTerm+')' from @term
set @FulltextTerm=SUBSTRING(@FulltextTerm,5,99999)


-- @FulltextTerm will have the below value:-
-- (light NEAR aluminum) OR (lightweight NEAR aluminum)

SELECT *
FROM dbo.Articles AS a
INNER JOIN  
   CONTAINSTABLE (dbo.Articles,ArticleBody,@FulltextTerm) AS ct 
   ON ct.[Key] = a.ArticleId

off course in your case you dont need the table variable @term, you could replace it with your Term table, but I only used it here to show the idea.

I believe this may be better than looping.

Note: I dont know the database version you have but you could even use the below if you can use STRING_AGG function

select @FulltextTerm=STRING_AGG('('+FulltextTerm+')',' OR ') from @term

How to use a column name in CONTAINSTABLE for the search condition?

1 Answers1