Thread: Sort by relevance

Hello,

I am trying to use a perl script to break an input string into keywords and
pull out all the records that match any of them:

my (@chips)= split(' ', $f_name);
foreach my $chip (@chips) {
    next if ($stoppers=~/ $chip / || length($chip) < 3);
    if ($query) {$query .= " OR name ~* '.*$chip.*' ";}
    else {$query = "SELECT * FROM info WHERE name ~* '.*$chip.*' ";}
}

this works fine for pulling the records but I would like to sort them by how
many of the "chips" matched.

On a set like:

name
-------------
Bill Smith
Ted Smith
Ted Brown

an input of : Ted Smith
would give me a query string of:
        SELECT * FROM info WHERE name ~* '.*Ted.*' or name~*'.*Smith.*'

which returns all records - which is what I want - but I would like to have
Ted Smith in the first row followed by the other records grouped by name and
sorted on another column (city or state in the real table)

Thanks for your help,
Chad McKay