Clever Geek Handbook

Partial Keyword Search (MS SQL 2005)

I currently have a stored procedure whose main purpose is to perform a full text search through a movie and TV show database table. To get it to search for a private keyword, I added some code in SQL to split the search query by spaces and output a statement like this:

'"batman *" ~ "be *"'

For example, the original string "batman be" can be generated from a text box on a page when user input and in every keyup javascript event, I send everything in that text box to a saved proc for results to get results by type (eg , autocomplete). In this case, the user may have searched for "Batman Begins" or "The Batman: Batgirl Begins" (an episode of a TV show), and both should appear as a result.

Below is a sample of my request. @partialKeywordString, in the above example, "batman *" ~ "be *" '.

SELECT f.title
FROM Films f INNER JOIN
    CONTAINSTABLE(Films, Title, @partialKeywordString) f_key ON f.filmid = f_key.[key]
ORDER BY f_key.Rank DESC

The problem with the query is that the ranking does not seem to be what I expect. If I were just looking for "batman", one would think that all movie titles starting with or only containing the word "batman" would be displayed first. But they don't. A sample of the result of what happens when you only search for "batman" is below:

"Batman: The Animated Series - Episode 114" "The Adventures of Batman and Robin - Episode 218" "Batman and Robin - Episode 101" "Batman - Episode 101" "Batman and Robin - Episode 204"

Much further down the list is the movie I was looking for - Batman Begins, or even just Batman.

I'm looking for advice on how to set up this query - I'm definitely not a SQL expert and I feel like I just processed the above code to get it working. I have a feeling for a more elegant or powerful solution and haven't found one yet.

Thank you in advance

+1


source to share


2 answers


After some research, I will try to use Lucene.Net for my movie title search engine rather than relying on full text search in SQL Server 2005. Early testing shows that the results were better and more relevant to Lucene. Searching for "batman" returns the following partial result set:



  • Batman
  • Batman start
  • Return of Batman
  • Batman and Robin: Batman Going Through
  • Batman Beyond: Touch of Curaré
  • Batman Beyond: Babel
  • Batman: Season 02
  • The Batman: Topsy Turvy
  • Batman and Robin: Tunnel of Terror
  • Batman Beyond [Animated Series]
  • The New Adventures of Batman: Curses! Again again!
  • New Batman Adventures: It looks like work for Bat-Mite!
+1


source


I think SQL Server Full Text works just as well, but you have to understand how to create keywords. This is not the same as Lucene, especially in terms of indexing. I think you will find that SQL Server will do better in terms of scalability and features, especially in SQL 2008, it is now part of the engine.



0


source






More articles:


All Articles