Matches a value to a set of generated values with bitmap chunks iteration.

scalar-expression %FIND valueset [SIZE ((nn))]

The %FIND predicate allows you to filter a result set by selecting those data values that match the values specified in valueset, iterating through values in a sequence of bitmap chunks. This match is successful when a scalar-expression value matches a value in valueset. If the valueset values do not match any of the scalar expression values, %FIND returns the null string. This match is always performed on the logical (internal storage) data value, regardless of the display mode.

%FIND, like the other comparison conditions, is used in the WHERE clause or the HAVING clause of a SELECT statement.

%FIND enables filtering of field values using an abstract, programmatically specified set of matching values. Specifically, it enables filtering of RowId field values using an abstract, programmatically specified bitmap, where valueset behaves similar to the subscript layer of a bitmap index.

The user-defined class is derived from the abstract class %SQL.AbstractFind. this abstract class defines the ContainsItem() boolean method. The ContainsItem() method matches the scalar-expression values to the valueset values.

Iteration through values in a sequence of bitmap chunks is performed using the following three methods:

Refer to %SQL.AbstractFind for further details concerning these four methods.

%FIND uses the same collation type as the column it is matched against. By default, string data type fields are defined with SQLUPPER collation, which is not case-sensitive. The “Collation” chapter of Using Caché SQL provides details on defining the string collation default for the current namespace and specifying a non-default field collation type when defining a field/property. If you assign a different collation type to the column, you must also apply this collation type to the %FIND substring. Refer to %SQLUPPER for further information on case transformation functions.

The optional %FIND SIZE clause provides the integer nn, which specifies an order-of-magnitude estimate of the number of values in valueset. Caché uses this order-of-magnitude estimate to determine the optimal query plan. Specify nn as one of the following literals: 10, 100, 1000, 10000, etc. Because nn must be available as a constant value at compile time, it must be specified as a literal in all SQL code. Note that nesting parentheses must be specified as shown for all SQL, with the exception of Embedded SQL.