Thread: Full Text Search modification

After implementing full text search, it turns out that it handles multiple terms as if there were "or" connectors between them.
Example: search for Red Apple and you get all items with red and all items with apple.

Can someone advise how to change this to be handled as if there were "and" connectors between the terms? This way, searching for Red Apple would only result in items with BOTH Red and Apple in the title.

This seems like it would be a simple change but I do not know where to begin with this. Any help is appreciated!

FTS utilizes a word breaker and stemmer to locate word boundaries, and generate inflectional forms of each word in the search term, so for example, "running", "ran", and "runner" all match the word "run".

It assigns a rank to each search result, based on the probability of a match. The result-set includes perfect matches, as well as imperfect matches with a lower Rank.

For example, a Red Apple product might be returned for any of the following queries, in order of Rank:

Red Apple
Red Apples
Red-colored Apples
Dark Red Apple
Apple
Red Beans

You could modify the stored procedure to only return high-ranking results. However, the number of high-ranking results would be different for a site with 12 products, versus a site with 12,000, so a lot of testing would need to be done.

Originally Posted by ASPDNSF Staff - Jon

FTS utilizes a word breaker and stemmer to locate word boundaries, and generate inflectional forms of each word in the search term, so for example, "running", "ran", and "runner" all match the word "run".

It assigns a rank to each search result, based on the probability of a match. The result-set includes perfect matches, as well as imperfect matches with a lower Rank.

For example, a Red Apple product might be returned for any of the following queries, in order of Rank:

Red Apple
Red Apples
Red-colored Apples
Dark Red Apple
Apple
Red Beans

You could modify the stored procedure to only return high-ranking results. However, the number of high-ranking results would be different for a site with 12 products, versus a site with 12,000, so a lot of testing would need to be done.

Do you have the name of the SP?

Here is what I was able to do in order to get a result set that is more in line with what you seem to be looking for.

If you take a look at the Scalar-valued function “GetValidSearchString” in the database, you will see the word “or” inside the routine. Change that word to “and” and see if that helps. Basically, right click on the function and select “Script Function As” and then select “ALTER To”. This will generate the Alter query which you can then execute.

I am very pleased with the results I am now getting by doing that. It feels more Google-like.

Hope that helps...
David