search results including percentage

[RestonDeveloper]

I've been tapped to build an internal profile search.
In the search text if they search 4 words for example.

I want to return profiles that have all four 100% match, (3 of 4) 75% match, %50, %25.

I'm just wondering if someone has done this before and can pass along any pointers. also i'd be searching every field in the user profile, so I'd need to loop through each field.

Is this purely a SQL solution? I was thinking it would be since I am basically returning the name of the profile, (eventually a profile snippet that has at least one search term.) and the percentage of match.

 

[ESquared]

create table profileinfo(userid int, info1 varchar(100), info2 varchar(100))
insert profileinfo values (1, 'soccer', 'green')
insert profileinfo values (2, 'baseball', 'red')
insert profileinfo values (3, 'football', 'blue')

create proc searchprofiles
   @searchinfo varchar(100)
as
select
   userid,
   totalfields = count(*),
   matchcount =
      sum(
         case when
            case infonum when 1 then info1 when 2 then info2 end
         like '%' + @searchinfo + '%' then 1
         else 0 end
      ),
   matchpercent =
      sum(
         case when
            case infonum when 1 then info1 when 2 then info2 end
         like '%' + @searchinfo + '%' then 1
         else 0 end
      )
      * 1.0 / count(*)
from
   profileinfo p
   cross join (select infonum = 1 union all select 2) x
group by userid
having
      sum(
         case when
            case infonum when 1 then info1 when 2 then info2 end
         like '%' + @searchinfo + '%' then 1
         else 0 end
      ) > 0

exec searchprofiles 'l'

For this sort of thing it's probably best if your table was like this:

[tt]userid infoid info
1 sport football
1 color blue[/tt]

Then it would be a LOT simpler to query, and more efficient, too.