Here's the approach I used for searching through a table that needed to
search 7 different fields in the table and weight them differently:
I run through this expression for each word that's split from the
searchstring and through each field with different weights given to each
field. Then there are actual several varitions on this that account for
words that are partial matches or words containing the search term (all
just deal with varrying the spaces before and after the field name.
I add all of those expressions up as the "relevance" and then sort by
relevance desc. Works very well because it takes the following things
into consideration: The location at which the search string appears in
the searched field (So a higher number is returned if the search string
appears right at the beginning of the field, a lower amount to zero if
the string appears further into the searched field, zero if it doesn't
exist.
$exact_match_weight = 3;
$SQL .= " if(instr(UPPER(concat(' ',field_name,' ')) , UPPER('
$string ')),(length(field_name)*2 -
instr(
UPPER(concat(' ',field_name,' ')) ,
UPPER(' $string ')
) +1) /
length(field_name) *
$weight[$field_name] *
$exact_match_weight , 0) ";
Or basically this is what the query returns:
if ($string is in field){
# the following is returned as a weighted
(length of field * 2) - position string starts in field + 1
-----------------------------------------------------------
total length of field * weight of field
}
else{
0 is returned
}
as relevance
Hmm... I hope that made sense...
-Chris
On Mon, 14 May 2001, Bill Marrs wrote:
> I found FULLTEXT (MATCH/AGAINST) indexes and the SOUNDEX function. Are
> there other MySQL features that help with Fuzzy searching?
>
> Soundex seems very useful, but I'm not sure how to apply it to multi-word
> strings.
>
> I'd also be interested in pointers to general info on writing fuzzy searches.
>
> A Perl example would be great.
>
>
> I think what I want it a kind of combination of SOUNDEX and the relevance
> value from MATCH/AGAINST. Is something like this possible?
>
> Thanks in advance.
>
> -bill
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---
----
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
