Hope someone here is more experienced than our current database programmer (no offense to him). We have a business search and directory website that just launched. It was developed using flex with msSQL as the database. Here is the problem...
We want the keyword field to be searched so that it finds records that are variations of the search term as well as multiple keywords. Here is an example to best explain...
Say this business has the following in its keyword (product/service) field: new home construction, remodeling, kitchen, bathroom, basement, addition, renovation, rehab, painting, pergola, deck, gazebo, commercial build-outs, basement finishing, modular, panelized, stick-built, site-built
Example Search #1 "new home painting" -- I should get this result because all three words were present in the keyword field, regardless of order. So basically it would be the AND operator so all terms are found, otherwise if it were OR then a search for "auto painting" would show this business and it isn't relevant.
Example Search #2: "new home renovating" -- Again should be found because we can use the inflectional feature of msSQL which would find "renovating" because it is a variant of the word "renovation" found in the search field.
Presently our programmer can only get one option to work. Either we get variants of the search word (dentist vs dentists) or multiple words ("new home painting" as in above example even though all three words aren't next to one another).
I've been told by them that the issue is using the AND operator to avoid getting countless irrelevant results. I am skeptical this cannot be done with full-text as the feature seems very robust.
From my understanding, there are two ways to approach this that should work. In both cases I am told we should be using FREETEXT instead of CONTAINS (although CONTAINS should also be working). The FREETEXT should be specified with the language argument as English to ensure the proper thesaurus and noise words are considered.
From here there are two options. One is to use the boolean operator AND as discussed. If this is not working you may also eliminate this entirely by simply setting the ranking to whatever gives the best output results. In other words, don't use AND at all because the database will automatically rank the search results so those that have instances of all search terms (for example "low back pain") would be ranked highest while those with fewer of the search terms (say, "low" only) would rank lower.
At this point the only consideration you would have is how to display these. Typically the results would display from highest ranking down but we are looking to randomize the results since we don't have the distance-based function working. So I assume you would display all records of a certain ranking then sort by zip code and randomize the results.
Anyway, please shed some light on this extremely frustrating dilemma. I am sure someone here is wise and experienced enough to overcome this issue.
Your insight and wisdom are appreciated in advance.