Comparison Predicate

  • Article

Note

Indexing Service is no longer supported as of Windows XP and is unavailable for use as of Windows 8. Instead, use Windows Search for client side search and Microsoft Search Server Express for server side search.

 

A comparison predicate uses arithmetic operators to compare column data to a literal value. For a row to be selected, the predicate must evaluate to TRUE. (Indexing Service queries that use such predicates are called relational property queries.) This predicate is an optional part of the optional WHERE clause of the SELECT statement.

SELECT Select_List | *
       FROM_Clause
       [WHERE Column_Reference Arithmetic_Operator Literal
             [Boolean_Operator Column_Reference Arithmetic_Operator Literal ...]]
       [ORDER_BY_Clause]

Parameters

Select_List

Specifies the list of column aliases (properties) making up the table (rowset) that is returned as a result of the query.

* (asterisk)

Specifies all columns. This option is valid only when the FROM_Clause parameter references a predefined view or a temporary view.

FROM_Clause

Specifies the files on which to perform the search. For details about this parameter, see FROM Clause.

Column_Reference

Specifies the column name (alias). Its data type must be compatible with the format of the literal specified.

Arithmetic_Operator

Specifies the arithmetic operator to use to compare the column reference data to a literal value. The following table lists the operators, their symbols, and examples.

Operator Symbol Example
Equals = WHERE DocAuthor = 'John Doe'
Not equals != or <> WHERE DocTitle != 'Finance'
Less than < WHERE WordCount < 1000
Greater than > WHERE WordCount > 500
Less than or equal to <= WHERE WordCount <= 500
Greater than or equal to >= WHERE WordCount >= 500

 

Literal

Specifies a constant data value that complies with the constraints of its data type. The following table lists the literal types that are supported.

Literal Type Description and Examples
Basic String The basic string literal can be an ANSI string constant or a Unicode string constant. Since strings are surrounded by single quotes, to specify a quote character as part of the string, use two single quotes. An empty string is denoted by two single quotes in succession (''). Examples: 
'Once upon a time'
'John''s children'
Exact Numeric An exact numeric literal can be an integer or a number with a decimal point. When an exact numeric literal is an integer, it can be one, two, four, or eight bytes in size. Examples: 
-300           (2 byte integer)
67000          (4 byte integer)
5000000000     (8 byte integer)
.55
-366.12
Approximate Numeric An approximate numeric literal is composed of a mantissa and a signed or unsigned exponent. Examples: 
2.5E-5
-.5E20
Hexadecimal A hexadecimal literal is an integer expressed in hexadecimal notation. The hexadecimal expression has to be prefixed with "0x". Example: 
0x2F456
Boolean A Boolean literal can only have the values TRUE or FALSE.
Absolute Date/Time The absolute date/time literal can be expressed as a date string or as a timestamp string. Note that the "/" (slash) and the "-" (dash) are interchangeable. Examples: 
'1996/01/25'
'1996/01/25 02:05:00'
'1996-02-28 10:59:03'
[!Note]
The Indexing Service engine requires dates to be in terms of file times based on Greenwich Mean Time (GMT).

Relative Date/Time The relative date/time literal expresses a date relative to the current date. The syntax for the DATEADD() function is: 
DATEADD( Time, Relative_Interval,
{ Relative_Date/Time | GETGMTDATE()) }
Time has the following values:
  • year or yy
  • quarter or qq
  • month or mm
  • day or dd
  • week or wk
  • hour or hh
  • minute or mi
  • second or ss
The GETGMTDATE() function is modeled after the GETDATE() function in Microsoft SQL Server™, but GETGMTDATE() indicates the current GMT rather than the current time on the local computer.
Use the following format to specify the relative date/time:
For example, to express an Indexing Service relative time value of -2 years, enter:
DATEADD(year, -2, GETGMTDATE())
To express a relative time value of - 3 years, 24 days, and 5 hours, enter: 
DATEADD(hh, -5, DATEADD(dd, -24, DATEADD(yy, -3, GETGMTDATE())))
[!Note]
Positive (future) dates are not supported. The Indexing Service engine requires dates to be in terms of file times based on GMT.

NULL A NULL literal is an undefined literal.
[!Note]
When determining whether a literal is defined, use IS and IS NOT instead of the equals operator ( = ).

 

Boolean_Operator

Specifies the Boolean operator to use to combine the comparison predicates. For details about the Boolean operators and precedence rules, see the Remarks section of WHERE Clause.

ORDER_BY_Clause

Specifies the ordering of the resulting rowset. This clause is optional. For details about this parameter, see ORDER BY Clause.

Examples

The following example returns all the files that have the phrase Financial Data as the title of their document.

... WHERE DocTitle = 'Financial Data'

The following example returns all the files that have the phrase Financial Data as the title of their document or that have John James as the author.

... WHERE DocTitle = 'Financial Data'      OR DocAuthor = 'John James'

The following example returns all the files that have a size less than 10,0000 or a word count less than or equal to 800.

... WHERE size < 10000
      OR DocWordCount <= 800

ARRAY Predicate

CONTAINS Predicate

FREETEXT Predicate

LIKE Predicate

MATCHES Predicate

NULL Predicate

WHERE Clause