Geek Answers Handbook

How can I use SQL Server full text search in multiple rows at the same time?

I am trying to improve search functionality on my web forums. I have a message table, and each post has (among other less interesting things):

  • PostID , a unique identifier for a single message.
  • ThreadID , identifier of the thread to which the message belongs. Each thread can have any number of messages.
  • Text , because the forum would be really boring without it.

I want to write an effective query that will search threads in the forum for a number of words, and it should return a hit for any ThreadID for which there are messages containing all the search words. For example, let's say that thread 9 has a post 1001 with the word "cat" in it, as well as message 1027 with the word "hat" in it. I want a cat hat search to return a hit for stream 9.

This seems like a direct requirement, but I don't know how to do it. Using the regular features of FREETEXT and CONTAINS for N'cat AND hat 'will not return any hits in the above example, because the words exist in different messages, although these messages are in the same thread. (As far as I can tell, when using CREATE FULLTEXT INDEX I have to give it my index on the primary key PostID and I can not say that it indexes all messages with the same ThreadID together.)

The solution that I am running now works, but sucks: maintain a separate table containing all the concatenated text text of each stream, and create a full text index on THAT. I am looking for a solution that does not require me to duplicate a copy of the text of each thread on my forums. Any ideas? Am I missing something?

+6
sql-server sql-server-2008
source share
2 answers

As far as I can see, there is no “easy” way to do this.

I would create a stored procedure that simply broke the search words and started looking for the first word and put threadid in the table variable. Then you look for other words (if any) in the threads you just collected (inner join).

If intrested I can write a few bits of code, but I assume that you do not need it.

+1
source share

What are you looking for? CAT HAT as a complete word, in this case:

CONTAINS(*,'"CAT HAT")

CAT OR HAT, then ..

 CONTAINS (*,'CAT OR HAT')

Searching for “CAT HAT” and only waiting for a post with CAT in makes no sense. If the problem is analyzing what the user enters, you can simply replace SPACES with OR (to search for any of the words, and if both are required). OR will provide you both entries for stream 9.

 SELECT DISTINCT ThreadId FROM Posts WHERE CONTAINS (*,'"CAT OR HAT")

Even better, if that helps, use brilliant irony ( http://irony.codeplex.com/ ), which translates (parses) the search string into a full-text query. Can help you.

You need to use google syntax for the original search, which can only be good, as most people use Google for search input.

Plus here is an article on how to use it. http://www.sqlservercentral.com/articles/Full-Text+Search+(2008)/64248/

0
source share

More articles:


All Articles