Posted by: R Manimaran | July 13, 2013

SharePoint 2010: FullTextSQLQuery – Using LIKE to have Startswith, Endswith and Contains

SharePoint 2010: FullTextSQLQuery – using LIKE to have Startswith,Endswith and Contains

I was working in a requirement to bring the SharePoint result using FullTextSqlQuery. When I used the FullTextSqlQuery with Contains operator, it used to return if the keyword we provided is the full word.

Below is the query text to get the Records which contains the word SharePoint.

SELECT Title,path,fileextension,write FROM scope() WHERE "scope" = ‘MyDocs’ AND IsDocument=1 AND Contains(Title,’SharePoint’)

If we use the half of the word like ‘rePoint’ in the above contains statement it will not result in any output.

As the above query resembles the SQL query I thought of using LIKE operator. When I tried to use the LIKE operator, it leads to No results found.

SELECT Title,path,fileextension,write FROM scope() WHERE "scope" = ‘MyDocs’ AND IsDocument=1 AND Title LIKE ’%SharePoint’

When I searched for the reason I have found the Metadata Property there is a Property option Reduce storage requirements for text properties by using a hash for comparison which is not enabled under Optimize Managed Property Storage section while creating a Managed Property.

After setting the above property I can able to use the LIKE operator.

To check ‘StartsWith’

SELECT Title,path,fileextension,write FROM scope() WHERE "scope" = ‘My Scope’ AND IsDocument=1 AND Title LIKE ’SharePoint%’

To Check ‘EndsWith’

SELECT Title,path,fileextension,write FROM scope() WHERE "scope" = ‘My Scope’ AND IsDocument=1 AND Title LIKE ’%SharePoint’

To Check ‘Contains’

SELECT Title,path,fileextension,write FROM scope() WHERE "scope" = ‘My Scope’ AND IsDocument=1 AND Title LIKE ’%SharePoint%’

Like Operator will work for the half word also. So if I want to check the items which starts with the text ‘S’, I can able to write the query as

SELECT Title,path,fileextension,write FROM scope() WHERE "scope" = ‘My Scope’ AND IsDocument=1 AND Title LIKE ’S%’

Following are the literals we can use with LIKE operator.

Note: After setting the “Reduce storage requirements for text properties by using a hash for comparison” property run the Full Crawl to get this change in effect.

Advertisements

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

Categories

%d bloggers like this: