In my last post, I gave a quick overview of the query syntax for the OLE DB Provider for Windows Search that ships in Windows Vista. In this post, I'll go over some of the details of the predicates that can be used in the WHERE clause.
Just a quick disclaimer: this post is meant to help you get started; it's not my intention to provide a complete reference of the entire syntax.
Simple predicates include LIKE, literal comparisons, and the NULL comparison.
Here are examples of the WHERE clause, using simple predicates:
WHERE "System.DisplayName" "System.ItemNameDisplay" LIKE 'a%' Returns all items whose name begins with the letter 'a'
[Updated on October 25, 2006 to reflect changes made to the property system since I wrote this post. The property System.DisplayName no longer exists; use System.ItemNameDisplay instead]
WHERE "System.DateModified" >= '2006-06-01'Returns all items modified on or after June 1st, 2006
WHERE "System.ApplicationName" IS NOT NULLReturns all items that have a value for System.ApplicationName
The full-text predicates, CONTAINS and FREETEXT, enable you to search for text within the contents and/or the properties of items.
They both have a similar syntax:
CONTAINS | FREETEXT ('<searchString>') searches within the contents of items
CONTAINS | FREETEXT (<column>, '<searchString>') searches within a given property
CONTAINS | FREETEXT (* , '<searchString>') searches contents and all properties
Here are some simple examples, using different combinations of the CONTAINS predicate:
CONTAINS(*, 'Catherine') will return all items that contain 'Catherine' in either its contents or in any of its properties.
CONTAINS("System.Author", 'Catherine') will limit the search to the property specified as the first argument – in this case, System.Author.
CONTAINS('Catherine') will limit the search to the item contents.
Note the following:
Regarding the format of <searchString>, if you want to search for anything beyond a single word with no spaces, you will also need to use double quotes. This is in addition to the single quotes that enclose the entire condition. Here are some examples:
CONTAINS(' "This is a phrase" ') note that the search phrase is within double quotes, and the entire argument within single quotes
CONTAINS(' "Using a wildcard*" ') uses * as a wildcard in the search string
CONTAINS(' "Using" AND "Boolean" OR "operators" ')will return items that contain the string 'using' and the string 'boolean' or the string 'operators'.
CONTAINS(' "This" NEAR "that" ') will return items that contain the string 'this' within approximately 50 words or less of the string 'that'.
Use FREETEXT if you care about relevancy– it’s the only predicate that returns a meaningful rank value. (All other predicates return 0 or 1000, depending on the whether there was a match or not). (Hint: to get the rank value, include System.Search.Rank in the select list)
Search Depth Predicates
Not sure what the official term for this group of predicates is, but the SCOPE and DIRECTORY predicates enable you to control the depth of the search.
The syntax for these two predicates is the same:
"SCOPE | DIRECTORY" = '(file | mapi | csc):<path>'
Here’s an example of a SCOPE predicate that limits the search to the C:\Foo\Example folder:
To only search the root folder, here’s what the DIRECTORY predicate would look like:
The syntax may seem a bit odd (or at least, it did to me); let me point out some of the details:
A Couple of Closing Hints
The Windows Vista search and indexing infrastructure shares a common lineage with the SharePoint Portal Server indexing technologies. I've found the SharePoint Portal Server Search SQL Syntax documentation to be quite useful in piecing together some of the syntax details. Just keep in mind that not everything is applicable to Windows Vista.
Regarding the Windows Vista property system - there have been recent changes to some of the property names to improve clarity, so you may need to change your queries to run with post-Beta 2 releases.