SharePoint: Using the CONTAINS Predicate with FullTextSqlQuery


The FullTextSqlQuery class is part of the Enterprise Search Query Object Model and is used to perform content queries using (amongst other things) managed properties and search scopes.

This functionality is used by the OOTB search components, but obviously you can use it for your own purposes too, and when doing so you will need to build up your own query statement, which is very much like (note: the syntax is like RDBMS SQL but certainly not the same), the RDBMS SQL query syntax.

The Enterprise Search SQL Syntax, supports the use of 2 full-text predicates which can be used as part of your WHERE clause, namely FREETEXT and CONTAINS.

This post is about the CONTAINS predicate and sumarises the syntactical forms I found actually worked, the documentation is misleading and some of the examples on the MSDN page, just don’t work, and cause the following exception when calling the Execute() method.

Exception: Your query is malformed. Please rephrase your query.

Microsoft.Office.Server.Search.Query.QueryMalformedException: Your query is malformed. Please rephrase your query.
at Microsoft.Office.Server.Search.Query.FullTextSqlQuery.Execute()

Sample Outcome Type
CONTAINS(Keywords,’keyword OR one’) Works Multiple keywords combined with boolean operators
CONTAINS(Keywords,’keyword OR “keyword one”‘) Works Multiple keywords and/or phrases combined with boolean operators
CONTAINS(Keywords,’keyword AND “keyword one”‘) Works Multiple keywords and/or phrases combined with boolean operators
CONTAINS(Keywords,’keyword*’) Works Keyword(s) with wildcards
CONTAINS(Keywords,'”keyword*”‘) Works Phrase(s) with wildcards
CONTAINS(‘keyword NEAR one’) Works Combining keywords with the NEAR function across all default property columns
CONTAINS(Keywords,’keyword NEAR one’) Works Combining keywords with the NEAR function across a specified column
CONTAINS(Keywords,’keyword one’) Fails
CONTAINS(Keywords,’keyword “”one””‘) Fails
CONTAINS(Keywords,’keyword “one”‘) Fails
CONTAINS(Keywords,’keyword “one two”‘) Fails
CONTAINS(Keywords,’keyword’ OR ‘one’) Fails
CONTAINS(‘keyword’ OR ‘one’) Fails
CONTAINS(‘keyword’ NEAR ‘one’) Fails

Published by

Phil Harding

SharePoint Consultant, Developer, Father, Husband and Climber.

One thought on “SharePoint: Using the CONTAINS Predicate with FullTextSqlQuery

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