Databases
StackOverflow2013
Postsdbo
POPartial Keyword Searching (MS SQL 2005)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPartial Keyword Searching (MS SQL 2005)
    primarykey
    data
    text
    <p>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:</p> <p><code>"batman*" ~ "be*"</code></p> <p>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.</p> <p>Below is a sample of my query. @partialKeywordString is, in the example above, <code>"batman*" ~ "be*"</code>.</p> <pre><code>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 </code></pre> <p>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.<br> A sample result of what happens when one searches for just "batman" is below:</p> <p>"Batman: The animated series - Episode 114"<br> "Adventures of Batman and Robin - Episode 218"<br> "Batman and Robin - Episode 101"<br> "The Batman - Episode 101"<br> "Batman and Robin - Episode 204" </p> <p>Much further down the list is the movie I was searching for--"Batman Begins" or even just "Batman".</p> <p>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.</p> <p>Thank you in advance</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USCarl
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POPartial Keyword Searching (MS SQL 2005)
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload