How do I use the column name in CONTAINSTABLE for a search term?
I was surprised to find that neither CONTAINS
nor CONTAINSTABLE
seem to support syntax like below where you pass the column name for the last parameter of the search term.
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 the error message "Incorrect syntax near" t ".
The table Terms
contains multiple rows with a column FulltextTerm
, and if any of these values FulltextTerm
are in the ArticleBody, it must match for the selected item to be selected. This is what I am trying to achieve.
CONTAINS
and CONTAINSTABLE
apparently 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 of course be much slower if I have to iterate over the table Terms
using a cursor or loop.
I am missing a trick here, or workarounds that may suggest - a set based solution is preferred, i.e. avoiding loops.
source to share
How about merging all your terms into one variable and then using CONTAINSTABLE like 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
Of course, in your case you don't need the @term table variable, you can replace it with your Term table, but I only used it here to demonstrate the idea.
I believe it might be better than looping.
Note: I don't know the version of the database you are using, but you could even use the one below if you can use the STRING_AGG function
select @FulltextTerm=STRING_AGG('('+FulltextTerm+')',' OR ') from @term
source to share