Tech Off Thread

5 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

SQL Server 2005 Full-Text Search

Back to Forum: Tech Off
  • User profile image
    Larsenal

    I am building a knowledge base and want to allow users to do a full-text search.  The search results should be sorted by relevance.

    I remember seeing a screencast or video which ran through the basics of FTS in 15 minutes or so.  For something that seemed relatively easy, I'm sure having a tough time finding any examples of how to do this.

    Help, T-SQL gurus!

  • User profile image
    W3bbo

    Well, first you need to set up indexing on the database first, then it's a simple matter of using one of the Freetext T-SQL commands.

    Here's an example using FreetextTable:

            SELECT
                Articles.[ID],[InContainer],[CompositeName], [Name],
                [UrlName], [Revision], [InUser], [Published],
                [Comments], [Overview], [DateCreated],
                [DateUpdated], [Pointer], [PointerTarget], [CommentCount], Username
            FROM
                Articles
                INNER JOIN Users ON Articles.InUser = Users.[ID]
                INNER JOIN freetexttable(Articles, ContentPost, @Query) As FTT ON FTT.[Key] = Articles.[ID]

  • User profile image
    Larsenal

    W3bbo to the rescue!

    I assume this would be sorted by relevance (hit count/density/etc...) by default?

    Also, what syntax do I use if I want to display (or otherwise use) that relevance value?

  • User profile image
    Matthew van Eerde

    The pseudo-table returned by FreeTextTable includes a "Rank" pseudo-column you can use for that purpose.  IIRC it ranges from 0 to 1000.

  • User profile image
    Larsenal

    Matthew van Eerde wrote:
    The pseudo-table returned by FreeTextTable includes a "Rank" pseudo-column you can use for that purpose.  IIRC it ranges from 0 to 1000.


    Thanks!  That's the answer I was looking for.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.