Searching a table -- sql server full text search

Hilary Cotter
3/29/2004 7:59:35 AM

It could be, but SQL FTS is optimized for searching for textual content
within char, varchar, text, and their unicode variants and image columns.

It looks like you are actually searching a single token when your column
contains a single token, as opposed to searching for a token when your
column contains many tokens.

What your query will return is an unranked list of hits where one or more
search terms are found in your Full Text indexed table. You would have to
manually go through the results of this list and rank them

I'm not exactly sure how to write a query that will score your table on the
number of matches per row.

Here is a way crude way to do it, this is an illustration using the authors
table
--create a temporary table, the first column is the pk of the table we are
searching on
create table counter
(au_id char(11),
counter int)
insert into counter
select au_id, 1 from authors where au_lname='ringer' -- here we are checking
for a match on the first column
--here we are checking for a match on the second column
update counter set counter=counter+1 from counter, authors where
counter.au_id=authors.au_id and authors.au_fname='albert'
--continue for each column you are searching.
the problem is that you have to do n table scan's for each column you are
trying to match which is very expensive

[quoted text, click to view]

anonymous NO[at]SPAM discussions.microsoft.com
3/29/2004 9:14:07 PM

ok what about the following query. Every time i execute
this query it returns 0 rank against each row (now FTS is
applied to only address, prevAddress1 and prevAddress2
cloumns):

mySQL = "Select customer.*, tAddress.[Rank] from
Customer, "&_
"FREETEXTTABLE(Customer,
*, '"&pPresentAddress& " "&pPreviousAddress1&" "&pPreviousA
ddress2&"') as tAddress "&_
"Where [Title] = '"&pTitle&"' and
FirstName='"&pFirstName&"' and
MidName='"&pMiddleName&"' "&_
"and SurName='"&pSurname&"' and
DOB='"&pDateofbirth&"' and
AccountNo='"&pAccountNumber&"' "&_
"and SortCode='"&pSortCode&"' and
tAddress.[Key] = idCustomer"
[quoted text, click to view]

Hilary Cotter
3/30/2004 7:23:53 AM

OK, this is a little better, but the ranking mechanism is necessarily going
to reflect the number of hits.

So if a row contains the PresentAddress, PreviousAddress1, and
PreviousAddress2 the ranking may be lower than a row that contains only
PresentAddress. This is due to the way the ranking algorithm works which is
to score based on frequency of word use. Rank is skewed to give terms which
occur slightly more than average higher weights than words which occur more
or less frequently.

The fact that you are getting 0 might mean that your table is not populated.
Did you populate it? Any messages in the event log from mssearch or mssci?

[quoted text, click to view]

anonymous NO[at]SPAM discussions.microsoft.com
3/31/2004 1:36:10 AM

can I have a query or some code so that it solves my
problem more efficiently. Because there are thousands of
records in the customer table and I dont want to take any
risks.

I want to have the percentage to which the retrived record
matched the given criteria. Please help me in this regard.
I'll be very thankful for your kind help.

Regards

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server full text search

sql server full text search : Searching a table