Two years back in this post on fulltext search in the catalog system, I had explained how fulltext searches are performed in the catalog system and added the following comment

"Providing an increased number of search features/conditions is something we are looking forward to, in the upcoming releases. This should allow you to specify more complex search phrases."

Commerce Server 2007 now allows you to specify complex search phrases which allow you to customize your searches to the finest level of granularity. If the default freetext search behavior as described in the above link does not work for you, you can now  use the new AdvancedFreeTextSearchPhrase property on the CatalogSearch object. An example of using this property to implement Thesaurus support is provided in this link. Note that when you set this property you should also set the UseAdvancedFreeTextSearch to true. This is to avoid ambiguity on whether the value specified for AdvancedFreeTextSearchPhrase or FreeTextSearchPhrase  property should be used to perform the search.

Here are some additional search scenarios for which you can use this new property.

• You can perform wild card searches by appending * to the search phrase. For eg to return all the products containing search terms beginning with "Commerce". When passing a compound phrase you need to enclose the phrase in quotes.

• To perform exact searches you should enclose the phrase in quotes. For eg to return rows that contain the exact phrase "Commerce Server 2007"

          catalogSearch.AdvancedFreeTextSearchPhrase  = "\"Commerce Server 2007\"";

• To return all the products containing the phrases "Commerce Server" and "Sql Server"

        catalogSearch.AdvancedFreeTextSearchPhrase=" \"Commerce Server 2007\" AND \"Sql Server\" ";

• To return all the products containing the phrases "Commerce Server" OR "Sql Server"

         catalogSearch.AdvancedFreeTextSearchPhrase=" \"Commerce Server 2007\"  OR  \"Sql Server\" ";

• To return all products beginning with Commerce Or Sql server

       catalogSearch.AdvancedFreeTextSearchPhrase=" Commerce*  OR \"Sql Server\" ";

•  You can also perform proximity searches using the NEAR keyword. For eg to return all products containing the word 2007 near "Commerce Server"

         catalogSearch.AdvancedFreeTextSearchPhrase="2007 NEAR \"Commerce Server\"";

•  You can perform inflectional searches using the INFLECTIONAL keyword. For eg to return all products with forms of dry like drying, dried etc

      catalogSearch.AdvancedFreeTextSearchPhrase="FORMSOF (Inflectional,dry)";

•       To return all products containing Commerce or Sql Server and not ASP

     catalogSearch.AdvancedFreeTextSearchPhrase="(\"commerce \" OR \"sql server\") and not ASP\"";  

The above are just some of the scenarios. To truly understand what else you can do take a look at this link. Determine the clause which best reflects your search criteria and pass it to the AdvancedFreeTextSearchPhrase as in the above example. 

For a complete code sample and additional filtering using other criteria like "list price < $100 AND IsActive=1"  see this post.