Current, I've got a stored procedure that has a main goal of doing a full text search through a database table of films and tv shows. In order to get it to do partial-keyword searching, I added some code in SQL to split up the search query by spaces, and output a statement like the following:
' "batman*" ~ "be*" '
The original string, "batman be", for instance, could be generated from a textbox on the page as the user is typing and on each javascript keyup event, I send whatever is in that textbox to the stored proc for results to get results as I type (like autocomplete). In this case, the user may have been looking for "Batman Begins", or "The Batman: Batgirl Begins" (a TV show episode) and they both should show up as a result.
Below is a sample of my query. @partialKeywordString is, in the example above, ' "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 I have with the query is that the ranking doesn't seem to be exactly what I'd expect. If I were to just search for "batman", one would believe that all movie titles, beginning with, or only containing the word "batman" would appear first. But they don't. A sample result of what happens when one searches for just "batman" is below:
"Batman: The animated series - Episode 114" "Adventures of Batman and Robin - Episode 218" "Batman and Robin - Episode 101" "The Batman - Episode 101" "Batman and Robin - Episode 204"
Much further down the list is the movie I was searching for--"Batman Begins" or even just "Batman".
I'm looking for advice on how to tweak this query--I'm definitely not a SQL expert, and I feel like I just man-handled the above code to get it to work. I have a feeling there's a more elegant or powerful solution, and I just haven't found it yet.
Thank you in advance