Only the wildcard characters that are appropriate to the data source of the layer or table you are querying are displayed. You can click the button to enter the wildcard into the expression you're building.
Wildcard characters appear as buttons on the Select by Attributes and Query Builder dialog boxes. The wildcards you use to query personal geodatabases are * for any number of characters and ? for one character. The wildcards above work for any file-based data or ArcSDE geodatabase. Alternatively, if you want to search with a wildcard that represents one character, use _.įor example, this expression would find Catherine Smith and Katherine Smith: OWNER_NAME LIKE '_atherine smith' % means that anything is acceptable in its place: one character, a hundred characters, or no character. For example, this expression would select Mississippi and Missouri among U.S. Use the LIKE operator (instead of the = operator) to build a partial string search. Personal geodatabases, for example, have functions named UCASE and LCASE that perform the same operation. Other data sources have similar functions. For file-based data sources like file geodatabases or shapefiles, use either the UPPER or LOWER function.įor example, the following expression will select customers whose last name is stored as either Jones or JONES: UPPER(LAST_NAME) = 'JONES' To make a case-insensitive search in other data formats, you can use a SQL function to convert all values to the same case. Strings in expressions are case sensitive except when you're querying personal geodatabase feature classes and tables. Strings must always be enclosed within single quotes. They also select the relevant SQL keywords and operators for you. They list the proper field names and values with the appropriate delimiters. To query an ArcSDE geodatabase, you use the SQL syntax of the underlying DBMS (that is, Oracle, SQL Server, DB2, Informix, or PostgreSQL).ĪrcGIS dialog boxes in which you create SQL WHERE clauses will help you use the correct syntax for the database you're querying. To query personal geodatabases, you use the Microsoft Access syntax. To query file-based data, including file geodatabases, coverages, shapefiles, INFO tables, dBASE tables, and CAD and VPF data, you use the ArcGIS SQL dialect that supports a subset of SQL capabilities. The SQL syntax you use differs depending on the data source. This would select the features containing "Alabama" in a field named STATE_NAME. A basic SQL WHERE clause would look like STATE_NAME = 'Alabama' The next part of the expression is the WHERE clause, which is the part you must build.
In most ArcGIS dialog boxes where you build a query expression, the name of the layer or table is supplied for you (or you select it from a drop-down list). See SQL reference for query expressions used in ArcGIS for information on subqueries. For this reason, keywords, such as DISTINCT, ORDER BY, and GROUP BY, cannot be used in an SQL query in ArcGIS except when using subqueries. Optionally, parentheses () can be used for defining the order of operations in compound queries.īecause you are selecting columns as a whole, you cannot restrict the SELECT to return only some of the columns in the corresponding table because the SELECT * syntax is hard-coded. Here is the general form for ArcGIS query expressions:įor compound queries, the following form is used: Query expressions use the general form that follows a Select * From Where clause (for example, the part of the SQL expression that comes after SELECT * FROM WHERE). SELECT * FROM forms the first part of the SQL expression and is automatically supplied for you. For a more detailed reference, see SQL reference for query expressions used in ArcGIS.
#Desktop covers buildings how to#
This topic describes how to build basic WHERE clause expressions and is useful if you are just getting started with SQL. For example, you use this syntax using the Select By Attributes tool or with the Query Builder dialog box to set a layer definition query. Query expressions in ArcGIS adhere to standard SQL expressions. Query expressions are used in ArcGIS to select a subset of features and table records.
0 kommentar(er)
