Order by Relevance

Posted on 2004-09-19
Last Modified: 2008-02-01
First off, sorry. 45 points is all the points I have left. :(

One of my tables has 3 text columns. I'm searching these three columns for words. I want to order the results by the total number of words found in all three columns combined.

Is it possible? I don't want to use VBScript to reorder them, especially if I have 1000 results.

Question by:nightzeus
  • 4
  • 2

Author Comment

ID: 12097647

Author Comment

ID: 12097696
Nevermind.. It seems to be part of another Microsoft program.

LVL 15

Accepted Solution

jdlambert1 earned 45 total points
ID: 12097854
No, no, ContainsTable is your best solution, and it's part of SQL Server. I wasn't going to post anything, since it looked like you were on the right track, and you could delete the question and save your points if you finished on your own.

Run "sp_fulltext_database enable" to turn on full-text indexing on the database.

If you get an error message it's probably because full-text indexing wasn't installed. Put in your CD and start the install process, and choose "Upgrade, remove, or add components to an existing instance of SQL Server." when it gives you that choice. Then in the Select Components window, select Server Component on the left, then check "Full-Text Search" on the right.

Once it's installed and the database is enabled, you have to enable the table:
sp_fulltext_table [ @tabname = ] 'qualified_table_name'
    , [ @action = ] 'action'
    [ , [ @ftcat = ] 'fulltext_catalog_name'
    , [ @keyname = ] 'unique_index_name' ]

Once you have the table enabled, you have to enable each of the three columns you want to use for your relevance ranking.

Then you can write a SELECT query with a ContainsTable clause.

See SQL Server Books Online (BOL) for sample code and more info.
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery


Author Comment

ID: 12097875
Thank you. :)
LVL 15

Expert Comment

ID: 12097917
You're welcome. I forgot to post the procedure for enabling columns:

sp_fulltext_column [ @tabname = ] 'qualified_table_name' ,
    [ @colname = ] 'column_name' ,
    [ @action = ] 'action'
    [ , [ @language = ] 'language' ]
    [ , [ @type_colname = ] 'type_column_name' ]

Author Comment

ID: 12097922
:))) Thanks again.


Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now