IQueryFilter.WhereClause Property

The where clause for the filter.

[Visual Basic .NET]
Public Property WhereClause As String

[C#]
public string WhereClause {get; set;}

[C++]
HRESULT get_WhereClause(
  System.String* WhereClause
);

[C++]
HRESULT put_WhereClause(
  BSTR WhereClause
);

[C++]
Parameters
WhereClause [out, retval]

  WhereClause is a parameter of type BSTR*
WhereClause [in]

  WhereClause is a parameter of type BSTR

Product Availability

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%'

 Use ISQLSyntax::GetSpecialCharacter to 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.

Use ISQLSyntax::GetSpecialCharacter to 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.

See Also

.NET Samples