CODEWITHSUNDEEP
sqlsql-serversql-server-2008full-text-search
Diskdrive
Diskdrive

Reputation: 18825

Can I use full text search a row in a table where the condition is an array of values from a select query?

I want to create a view and I want to do a full text search of a row using a set of keywords. These keywords exist in a table in the database.

So is it possible to do something like below where I can use a select statement to dynamically determine which keywords to filter on.

SELECT * FROM table1
WHERE CONTAINS(Row1, 
'[SELECT k.Name FROM KeywordCategory kc
inner join Keyword k
on kc.KeywordId = k.Id
where kc.Category in ('BrandA', 'BrandB', 'BrandC')]')

Upvotes: 1

Views: 83

Answers (2)

Keith
Keith

Reputation: 21244

The CONTAINS search condition cannot reference other tables, but you can get around this limitation by constructing a variable from the keywords.

-- build search condition, example: '"keyword1" OR "keyword2" OR "keyword3"'
declare @SearchCondition nvarchar(4000)
SELECT  @SearchCondition = IsNull(@SearchCondition + ' OR ', '') + '"' + k.Name + '"'
FROM KeywordCategory kc
inner join Keyword k on kc.KeywordId = k.Id
where kc.Category in ('BrandA', 'BrandB', 'BrandC')

SELECT  *
FROM    table1
WHERE   Contains(*, @SearchCondition)

You won't be able to do this in a view though, so you would have to write it as a function or stored procedure.

Upvotes: 1

Juan
Juan

Reputation: 1382

Hi Yes you can use contains with the full text search indexed column. If you want to have two words one near the other you can use contains 'keyboard1' near 'keyboard2' etc

Upvotes: 0

Related Questions