Solved

Order by Relevance

Posted on 2004-09-19
6
318 Views
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.

Thanks,
Richard
0
Comment
Question by:nightzeus
  • 4
  • 2
6 Comments
 
LVL 1

Author Comment

by:nightzeus
ID: 12097647
0
 
LVL 1

Author Comment

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

SharePoint
0
 
LVL 15

Accepted Solution

by:
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.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 1

Author Comment

by:nightzeus
ID: 12097875
Thank you. :)
0
 
LVL 15

Expert Comment

by:jdlambert1
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' ]
0
 
LVL 1

Author Comment

by:nightzeus
ID: 12097922
:))) Thanks again.

Richard
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

773 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