Patrick's SharePoint Blog

SharePoint's Booming world

FullTextSQLQuery and the Like statement in SharePoint 2010

Posted by Patrick Boom on June 29, 2011

At my current client, we encountered an issue with some of the custom search web parts. In these web parts, we would perform a custom FullTextSQLQuery against the search index using the Like statement against custom created managed properties. Somehow, the search did not return any results, no matter what the keyword was.

This behavior was kind of strange and indicated that there must be something wrong with the creation of the managed properties, or the population of these properties. Several steps were taken, like again executing a full crawl, remapping the managed properties and so on.

Still, the search did not return any results. This drove me a little bit nuts until I found a post in the social MSDN by Steve Curran. It turns out, Microsoft changed the behavior a little bit since SharePoint 2010. Managed properties on SharePoint 2010 have an additional setting called ‘Reduce storage requirements for text properties by using a hash for comparison’ that influence the way the query behaves when using the Like statement (or the contains statement for that matter). If not checked, any Like comparison against this managed property will always return null, no matter what you put in. Checked this setting, ran a full crawl again and search was working fine.

So thank you Steve!

Advertisements

5 Responses to “FullTextSQLQuery and the Like statement in SharePoint 2010”

  1. Srinivasulu said

    Hi ,

    I had an issue with the full-text index in the sql server .
    What i need is if i can search with the singular word it has to get the results for plural word to respective singular word.
    I have tried this by enabling the full-text index in the specific table.But i am not able to get those results exactly what we except .

    Please anyone help can be really appreciated in Advance.

    Thanks .

    • I guess that using the FullText option in SQL is not fulfilling this requirements, as the server has no notion of plural of singular forms.
      For what I know, FAST search for SharePoint is capable of doing this, not the Enterprise Search standard with SP 2010.
       

  2. nfatima said

    Keep posting good stuff

    Appreciated

  3. Sumon Khan Personal Blog…

    […]FullTextSQLQuery and the Like statement in SharePoint 2010 « Patrick's SharePoint Blog[…]…

  4. Greetings, wanted to give you a simple suggestion and permit you
    to find out that a portion of the pictures are not loading successfully within this web blog.
    I have no idea why nonetheless I believe it’s actually a linking matter. I’ve also tried this in 2 other internet browsers and both still present the identical result.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: