Ellen Hu

Ellen Hu

  • NA
  • 63
  • 0

SQL 2008 Text Search (FREETEXTTABLE, formsof, thesaurus )

Aug 5 2010 6:31 PM
Hi All,

I have problem with my search stored procedure.  My table (tblCharity) has almost 2,000,000 records. I need search by the order of Name, Mission.

Say my keyword ='American National red cross', there is no problem for me to pop up the first one with exact matching name.

Then ideally should come most match words, say has 'American Red Cross" or "Red Cross"... but the result seems different, it cannot identify the most match.

I used something like
@OrgSqlStatement = 'INNER JOIN CONTAINSTABLE(tblCharity, zName, ''formsof(thesaurus,"' + @OrganizationName + '")'') AS KEY_TBL
            ON C.zCharityId = KEY_TBL.[KEY]'
and INNER JOIN FREETEXTTABLE(tblCharity, zMission,@OrganizationName) AS KEY_TBL ON C.zCharityId = KEY_TBL.[KEY]

Any thoughts? Thank you in advance,

ellen4j