IQueryFilter Interface

Provides access to members that filter data based on attribute values and or relationships.

Description

IQueryFilterfilters data based on an attribute query. A string defining a where clause is required. An optional list of columns may be included to specify the column values to be retrieved. If no columns are specified, all values will be returned.

When To Use

When you need to filter data based on attribute values or the relationships between attributes.

Members

Name Description
Method AddField Appends a single field name to the list of sub-fields.
Read/write property OutputSpatialReference The spatial reference in which to output geometry for a given field.
Read/write property SubFields The comma delimited list of field names for the filter.
Read/write property WhereClause The where clause for the filter.

IQueryFilter.AddField Method

Appends a single field name to the list of sub-fields.

Public Sub AddField ( _
    ByVal subField As String _
)
public void AddField (
    string subField
);

Remarks

The AddField method can be used to add a field to the SubFields list before the query is executed. Adding a field will clear the default "*" value from the SubFields. Setting it back to this original "*" can be done either by setting Subfields to "*" or to "".

IQueryFilter.OutputSpatialReference Property

The spatial reference in which to output geometry for a given field.

Public Function get_OutputSpatialReference ( _
    ByVal FieldName As String _
) As ISpatialReference
Public Sub set_OutputSpatialReference ( _
    ByVal FieldName As String, _
    ByVal OutputSpatialReference As ISpatialReference _
)
public ISpatialReference get_OutputSpatialReference (
    string FieldName
);
public void set_OutputSpatialReference (
    string FieldName,
    ISpatialReference OutputSpatialReference
);

IQueryFilter.SubFields Property

The comma delimited list of field names for the filter.

Public Property SubFields As String
public string SubFields {get; set;}

Description

You can use the SubFields property to improve performance when using query filters. The performance gain comes from just fetching the field values that you require rather than all the data for each row. The default value for SubFields is "*", which indicates that all field values will be returned. Setting it back to this original (default) "*" can be done either by setting it to "*" or to "".

The SubFields property should be a comma-delimited list of the columns that are required. For example, to retrieve two fields named "OBJECTID" and "NAME", the property should be set to "OBJECTID, NAME" (the space is optional).

It isn�t necessary to set the subfields when the query filter is used in a context in which no attribute values are fetched, for example, when selecting features.

Remarks

When accessing rows from a subset of data defined by a query filter, all the fields will be present, but just those specified by SubFields will be populated with values. This ensures that the field index positions for an object remain constant no matter how it was hydrated.

When editing data, always use "*" for the SubFields property.

IQueryFilter.WhereClause Property

The where clause for the filter.

Public Property WhereClause As String
public string WhereClause {get; set;}

Description

The WhereClause property allows you to specify an expression which will constrain the features returned from the QueryFilter. For example, you can use the WhereClause property to select all the polygons with an area greater than 1,500 square units: "AREA" > 1500.

The expression specified with the WhereClause property is a SQL query. The syntax of the query differs depending on the data source you are using, as it is in the native format of the database or data source. An application can use the ISQLSyntax interface on a Workspace to determine information about the SQL syntax used, such as the delimiting character used in qualifying table and field names and the identifier quote character.

Field names

- If you are querying data in a file geodatabase, shapefile, dBase table, coverage, INFO table, then field names are enclosed in double quotes:"AREA"

- If you are querying data in a personal geodatabase then field names are enclosed in square brackets:[AREA]- If you are querying data in an ArcSDE geodatabase or an ArcIMS image service or feature service, then fields are not enclosed:AREA- If you are querying data in a worksheet in an Excel file (.xls file) or a text file (.txt file), fields are delimited in single quotes 'AREA' unless you are working in the Select By Attributes dialog launched from the table window, in which case square brackets [AREA] are used.

Use ISQLSyntax::GetSpecialCharacter to return the delimited identifier prefix and suffix for the data source.

Strings

Strings must always be enclosed within single quotes. For example:

"STATE_NAME" = 'California'

Personal geodatabases stored in Access are case insensitive to field values, whereas ArcSDE, File and shapefiles are case sensitive. To make a case insensitive search in other data formats, you can use a SQL function to convert all values to the same case. For file-based data sources, use either the UPPER or LOWER function.

For example, given a field value of "Florida", a WhereClause of "State_name = 'florida'" will return one USA state when run against a data in a personal geodatabase, but none with and shapefiles and ArcSDE. A WhereClause of "State_name = 'Florida'" will return one feature in all cases.

For example, the following expression will select customers whose last name is stored as either Jones or JONES:

UPPER("LAST_NAME") = 'JONES'

Other data sources have similar functions. Personal geodatabases, for example, have functions named UCASE and LCASE that perform the same function.

Use the LIKE operator (instead of the = operator) to build a partial string search. For example, this expression would select Mississippi and Missouri among the USA state names:

"STATE_NAME" LIKE 'Miss%'

UseISQLSyntax::GetSpecialCharacterto return the delimited identifier prefix and suffix for the data source.

Wildcard Characters

A wildcard character is a special symbol that stands for one or more characters.

For any file-based data, '%' means that anything is acceptable in its place: one character, a hundred characters, or no character. Alternatively, if you want to search with a wildcard that represents one character, use '_'.

For example, this expression would select any name starting with the letters Cath, such as Cathy, Catherine, and Catherine Smith:

"NAME" LIKE 'Cath%'

But this expression would find Catherine Smith and Katherine Smith:"OWNER_NAME" LIKE '_atherine smith'

The wildcards you use to query personal geodatabases are '*' for any number of characters and '?' for one character.

UseISQLSyntax::GetSpecialCharacterto return the wildcard specific for the data source being queried.

NOTE: If you use a wildcard character in a string with the = operator, the character is treated as part of the string, not as a wildcard.

With a joined table, use wildcards appropriate for the side of the join that you are querying. If the query only applies to fields in the target table (the left-side table), use the target table wildcards. If the query only applies to fields in the join table (the right-side table), use the join table wildcards. If the query involves fields from both sides of the join, use the '%' and '_' wildcards.

For example, if you join a dbf file (the join table) to a personal geodatabase feature class (the target table):

1) Use * for queries that only involve personal geodatabase fields.

2) Use % for queries that only involve dbf columns.

3) Use % for queries involving columns from both sides of the table.

The NULL keyword

Null values are supported in fields for geodatabases and for date fields in shapefiles/dBASE tables and coverages/INFO tables.

The Distinct keyword

The Distinct keyword is not supported by file geodatabases. The recommended workaround is to use the IDataStatistics::UniqueValues method to return the distinct values for a field.

Querying numbers

You can query numbers using the equal (=), not equal (<>), greater than (>), less than (<), greater than or equal (>=), and less than or equal (<=) operators.

"POPULATION96" >= 5000

Querying dates

The syntax required for querying dates depends on the data type. ArcMap will automatically write the proper syntax for you when you double-click a date value in the Unique Values list. See the SQL reference mentioned above for more about querying dates.

Reference

For more information on using SQL in a query filter, see the ArcGIS web help article, SQL reference.

Classes that implement IQueryFilter

Classes Description
ImageQueryFilter (esriCarto) An image query filter.
QueryFilter Esri Query Filter object.
SpatialFilter Esri Spatial Filter object.

Remarks

Unlike the QueryDef object, QueryFilter objects are supported across all workspace types, including shapefiles and coverages.

For datasets that support it, an ORDER BY clause can be set on the attribute query by using the IQueryFilterDefinition::PostfixClause property.

Your browser is no longer supported. Please upgrade your browser for the best experience. See our browser deprecation post for more details.