Search Condition Support in Index Services

The search_condition supported by Index Services is slightly different from the search_condition supported by SQL Server. Because these queries are distributed to the OLE DB provider for processing, the queries must follow the rules of the provider. The main difference is that OLE DB Provider for Index Services does not support the QUANTIFIED COMPARISON, BETWEEN, EXISTS, IN, or NULL predicates, but it does support two other predicates: MATCHES and ARRAY COMPARISON. These predicates are not yet directly supported by SQL Server.

Following is an introduction to the search_condition syntax as supported by Index Services. Some aspects have been omitted, and the syntax of other aspects is incomplete. For more information, see the Index Services documentation.

The search_condition syntax is as follows:

MATCHES and ARRAY COMPARISON Predicates

This section briefly introduces the MATCHES and ARRAY COMPARISON predicates. For more information, see your Windows NT documentation.

The MATCHES predicate provides more extensive pattern matching than the LIKE predicate. This added functionality bears the burden of a more complicated set of rules. The complete syntax and rules are not described here; rather, here are some examples to illustrate the use of this predicate.

Here is a grouped match against more than one pattern, where it is known that the author’s first name is Peggy, but the spelling of her second name is uncertain.

WHERE MATCHES( DocAuthor, 'Peggy |(MacK|,McK|,MacC|,McC|)arson' ) 

Here, it is uncertain if Pellow is spelled with one “l” or two.

WHERE MATCHES( DocAuthor, '* Pel|{1,2|}ow' )  

The ARRAY COMPARISON predicate is for use with the Index Services Vector properties. Some of the property values filtered by Index Services are multivalued. The data type of such values is a variable size array. SQL Server does not yet support such data types, but SQL-3 does. The SQL extension supported by OLE DB Provider for Index Services is consistent with that in SQL-3. For example, the virtual table contains a number of PowerPoint presentations, and you want to know the path to the presentations that contain any slide called “CONTAINS predicate,” “FREETEXT predicate,” or “Query Transformation.”

SELECT * 

FROM OpenQuery(FileSystem,

     'SELECT Path

      FROM SCOPE('' "/slide_presentations" '')

      WHERE DocPartTitles = SOME ARRAY[ ''CONTAINS predicate'',

                                        ''FREETEXT predicate'',

                                        ''Query Transformation'' ] 

     ') 

For more information, see your Windows NT documentation.