There is no such professional who could solve my problem?

[Sajid]

Hi all.

I am using ASP and SQL Server 2000

I have a requirement in which customer fills in a form. The provided form fields are searched against the coulmns in customer table. For example FirstName is searched against FirstName in the table and so on. If the form data
is same as that of a table row than it is 100%. If for example FirstName, LastName, sortcode, accountno are exactly the same but present address or previous address doesnt match than it should return an appropriate %age to
which these two are similar.

I have applied FTS on customer table but it usually returns 0 rank (although the records returned are correct but the rank is 0). And when I manually match the two records, they might be 70% similar. For example their firstname, lastName, sortcode, accountno are exactly the same but the previousAddress1 does not match at all. Now most of the fields match therefore the rank returned should be higher. I have applied FTS on all the mentioned fields.

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.

Customer Table(Structure
-----------------
SortCode(varchar 20),
AccountNo(varchar 20),
Title(varchar 10),
FirstName(varchar 50)
MidName(varchar 50)
SurName(varchar 50)
Address(varchar 255)
prevAddress1(varchar 255)
prevAddress2(varchar 255

And I'm using following query to retrieve the results

Select customer.*, tAddress.[Rank] from Customer, FREETEXTTABLE(Customer, *, 'Mr sajid Saleem 16/03/1970 AA-12345 ab-12 House 123, Street 123, City XYZ') as tAddress Where (Contains(FirstName,'sajid') or Contains(SurName,'Saleem')) and tAddress.[Key] = idCustomer order by tAddress.[Rank]

I'll be very thankful for your kind help.

Regards

[Manohar Kamath [MVP]]

Sajid,

This is more of an SQL server question, so perhaps you will have a better
luck in those newsgroups.

However, I will attempt to answer your question without getting into much
details. With your FT, you are retrieving ONE type of information -- what
rows qualify for your search results. You can use the primary keys of these
rows to query your tables again to check what %age the results match.

--
Manohar Kamath
Editor, .netWire
[url]www.dotnetwire.com[/url]


"Sajid" <sajid622@hotmail.com> wrote in message
news:67A10BBF-35FE-4594-903D-E3C9A5CB19D3@microsoft.com...

> Hi all.
>
> I am using ASP and SQL Server 2000
>
> I have a requirement in which customer fills in a form. The provided form

fields are searched against the coulmns in customer table. For example
FirstName is searched against FirstName in the table and so on. If the form
data

> is same as that of a table row than it is 100%. If for example FirstName,

LastName, sortcode, accountno are exactly the same but present address or
previous address doesnt match than it should return an appropriate %age to

> which these two are similar.
>
> I have applied FTS on customer table but it usually returns 0 rank

(although the records returned are correct but the rank is 0). And when I
manually match the two records, they might be 70% similar. For example their
firstname, lastName, sortcode, accountno are exactly the same but the
previousAddress1 does not match at all. Now most of the fields match
therefore the rank returned should be higher. I have applied FTS on all the
mentioned fields.

>
> 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.

>
>
> Customer Table(Structure)
> ------------------
> SortCode(varchar 20),
> AccountNo(varchar 20),
> Title(varchar 10),
> FirstName(varchar 50),
> MidName(varchar 50),
> SurName(varchar 50),
> Address(varchar 255),
> prevAddress1(varchar 255),
> prevAddress2(varchar 255)
>
>
> And I'm using following query to retrieve the results:
>
> Select customer.*, tAddress.[Rank] from Customer, FREETEXTTABLE(Customer,

*, 'Mr sajid Saleem 16/03/1970 AA-12345 ab-12 House 123, Street 123, City
XYZ') as tAddress Where (Contains(FirstName,'sajid') or
Contains(SurName,'Saleem')) and tAddress.[Key] = idCustomer order by
tAddress.[Rank]

>
> I'll be very thankful for your kind help.
>
> Regards
>

[Jeff Cochran]

On Mon, 5 Apr 2004 02:11:04 -0700, "Sajid" <sajid622@hotmail.com>
wrote:

>I am using ASP and SQL Server 2000
>
>I have a requirement in which customer fills in a form. The provided form fields are searched against the coulmns in customer table. For example FirstName is searched against FirstName in the table and so on. If the form data
>is same as that of a table row than it is 100%. If for example FirstName, LastName, sortcode, accountno are exactly the same but present address or previous address doesnt match than it should return an appropriate %age to
>which these two are similar.
>
>I have applied FTS on customer table but it usually returns 0 rank (although the records returned are correct but the rank is 0). And when I manually match the two records, they might be 70% similar. For example their firstname, lastName, sortcode, accountno are exactly the same but the previousAddress1 does not match at all. Now most of the fields match therefore the rank returned should be higher. I have applied FTS on all the mentioned fields.
>
>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.

Well, nobody can really help. How are you determining a match, and a
percentage assigned to it? Is a first name of Joseph and last name of
Smith 50% match to Joe smith? Even if they actually are the same
person? How about an address of 1600 Pennsylvania Ave versus 1600
Pennsylvania Avenue? It's 100% the same address, but the system won't
match it because "ave" does not match "Avenue".

If all you want is a straight field-for-field match, count the
database columns, count the columns that are exact matches and do the
division. If you want something that mimics what a person would
determine as a percentage of matching, then you'll have to write a lot
of back-end code to mimic a person's thought patterns.

Jeff