Full Text Search on Multiple search terms

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Full Text Search on Multiple search terms

Author

Topic

Will H
Yak Posting Veteran

56 Posts

Posted - 2009-12-07 : 14:50:46

I'm passing in a table of search terms to a procedure. There can be an arbitrary number of terms, say 1-500:

SELECT SearchValue FROM @Terms
'abc*'
'farming*'
'rodents*'

What I'd like to do is Use these terms in a full text search, using Contains. Something like this, although hopefully easier to write:


SELECT stuff
FROM Books
WHERE CONTAINS(*, 'abc*')
OR CONTAINS (*, 'farming*')
OR CONTAINS (*, 'rodents*')

I've got this to work with LIKE statements, but the slow performance is why I was using FTS anyway:


SELECT * 
FROM Books b
WHERE EXISTS (
SELECT *
FROM @Terms t
WHERE b.BookName LIKE t.Value
)

I've searched around a little and the only consensus seems to be Dynamic SQL. Is there any other way, or am I stuck with sp_executeSQL ?

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-07 : 15:09:35

Can you use a function to stuff the variables into a string...

something like -

Set @myString = '"abc" OR "farming" OR "rodents" OR ....'

Select Stuff
From Books
Where Contains(Stuff, @myString)

Will H
Yak Posting Veteran

56 Posts

Posted - 2009-12-07 : 15:46:03

That is so much simpler, thanks DP978!

One thing, do you know how operator precedence is handled in FTS? Specifically, are these two queries similar?


SELECT * FROM Books
WHERE CONTAINS(*, '"T*" AND "WEASEL*" OR "BE*" AND "STAR*"')


SELECT * FROM Books
WHERE CONTAINS(*, '"T*" AND "WEASEL*"')
OR CONTAINS(*, '"BE*" AND "STAR*"')

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-07 : 16:09:03

From my experience with Op. Precedence I would have to say yes they are similar, you would have to run a few examples to see which runs more optimally.

Be sure that if you use the 2nd manner you enclose all the OR CONTAINS arguments in parenthasis if you have additional AND statements. Else you will get unexpected results.

If you are using some method to 'generate' the @string and have ~500 terms make sure to take into account that the ncharvar can only hold 8,000 chars. If you exceed that you will need to use your 2nd method to break them up.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources