Windows Vista Search Syntax: WHERE are my predicates?

  • Article

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

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 NULL
Returns all items that have a value for System.ApplicationName

 

Full-Text Predicates

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:

  • The predicate argument is enclosed in parenthesis
  • The search string is always enclosed in single quotes
  • As we saw in the SELECT statement, column names are enclosed in double quotes
  • Searches are case-insensitive

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 SCOPE predicate will search a specific folder and all of its subfolders (sometimes referred to as deep traversal).
  • The DIRECTORY predicate will search a specific folder, without searching subfolders (or what’s sometimes referred to as a shallow traversal).

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:

"SCOPE"='file:C:/Foo/Example'

To only search the root folder, here’s what the DIRECTORY predicate would look like:

"DIRECTORY"='file:C:/Foo/Example'

The syntax may seem a bit odd (or at least, it did to me); let me point out some of the details:

  • "SCOPE"=
    • The predicate is enclosed in double quotation marks (it’s case insensitive, so it doesn’t matter if you use upper or lower case) and followed by an equal sign
  • 'file:C:/Foo/Example'
    • The path string is enclosed in single quotation marks
    • The path string begins with the corresponding protocol (in this case, file) followed by a colon; it's not followed by the familiar //, as in URLs.
    • Forward slashes (/) are used in the path

 

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.