Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to run a full text search on an FTS-indexed column for all values in another table?

Conceptually it'd look like

select d.* from Docs d, Tags t where CONTAINS(d.fulltext, t.tagvalue)

I'm rather new to MSSQL FTS, though I know that the equivalent would work in postgres

select d.* from docs d, tags t where to_tsquery(t.tagvalue) @@ to_tsvector(d.fulltext)

Thanks for the help!

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

Yes, but not directly. You would have to build dynamic SQL strings, one row for every join set (pair of Docs and Tags rows), and then execute those strings. That's probably not practical.

In the FROM query, if you had 1,000 records in Docs and 1,000 records in Tags, the result set would have 1,000,000 rows. I'm assuming you knew that and just dummied up some quick T-SQL - obviously this wouldn't work in production.

share|improve this answer
    
thanks. Indeed, this was just a q&d query and not even really in the domain of what I'm hoping to achieve. However, if I understand your response, it is that this isn't really a feasible implementation. Is that correct? –  swasheck May 16 '12 at 14:11
    
@swasheck Yep, I wouldn't try to implement something like that in production. –  Brent Ozar May 16 '12 at 14:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.