|
SQL Expressions in ArcMap
From ESRI ArcGIS Desktop Help
Building an SQL expression
Structured Query Language (SQL) is a powerful language you use to define one or more criteria that can consist of attributes, operators, and calculations. For example, imagine you have a map of customers and want to find those who spent more than $50,000 with you last year and whose business type is "Restaurant". You would select the customers with this expression: Sales > 50000 AND Business_type = 'Restaurant'.
In ArcMap, you can select features by searching with a Structured Query Language (SQL) expression. When you create an expression in the Select By Attributes dialog box, you create it in a version of SQL; the one you use depends on the format of the data you're querying. You use ANSI SQL with shared geodatabase data and Jet SQL with personal geodatabase data. These two versions of SQL are similar; however, each supports features and functions the other doesn't.
When you query coverages, shapefiles, INFO tables, and dBASE tables, you use a limited version of SQL that doesn't support functions or the many features of ANSI and Jet SQL. The following sections show you how to build simple expressions. For information on how to build more complex expressions, see your SQL reference manual.
Some special rules apply when building a SQL expression for joined layers and tables. For more information, see the Knowledge Base article, How to properly format an SQL expression to make selections in joined layers and tables.
Specifying fields If you're querying ArcInfo coverages, shapefiles, INFO tables, or dBASE tables, enclose fields in double quotes:
"AREA"
If you're querying personal geodatabase data, enclose fields in square brackets:
[AREA]
If you're querying shared geodatabase data, don't enclose fields:
AREA
Searching for strings Regardless of the data you're querying, always enclose strings such as names in single quotes. For example, this query will select California in a layer based on personal geodatabase data:
[STATE_NAME] = 'California'
Strings are case-sensitive for coverages, shapefiles, INFO tables, dBASE tables, and shared geodatabases. If you are unsure of case, use the operator LIKE, not '='. For example, "STATE_NAME" = 'rhode island' will not find a feature with "Rhode Island" in its attribute record, but "STATE_NAME" LIKE 'rhode island' will.
Strings are not case-sensitive for personal geodatabases. For example, [STATE_NAME] = 'rhode island' will select a feature with "Rhode Island" in its attribute record.
If you are uncertain of spelling or just want to use a shorter string in your expression, use LIKE with wildcards. If you are querying a coverage, shapefile, INFO table, dBASE table, or shared geodatabase:
'_' represents any one character and '%' any group of characters.
If you are querying a personal geodatabase:
'?' represents any one character and '*' any group of characters.
For example, this query will select Mississippi in a shared geodatabase:
STATE_NAME LIKE 'Miss%'
This query will find Catherine Smith and Katherine Smith in a layer based on personal geodatabase data:
[OWNER_NAME] LIKE '?atherine smith'
If you use a wildcard character in a string with the = operator, the character is considered part of the string, not a wildcard.
Searching for values To search for a specific value, use the = operator. To compare values use the <, >, <=, >=, and <> operators. For example, this query will select all houses greater than or equal to 1,500 square feet in a coverage:
"AREA" >= 1500
You can include calculations in expressions. Mathematical operators you can use include +-* and /. For example, to find the counties with a population density of less than or equal to 25 people per square kilometer, you could use the query:
"POP2000" / Area <= 25
Longer calculations evaluate * and / operators from left to right, then the + and - operators. For example, this query would multiply PRICE by AREA, then add TAX:
"NETVALUE" > "TAX" + "PRICE" * AREA
To control the order calculations evaluate, use parentheses. Calculations within parentheses evaluate first. For example, to add TAX to PRICE, then multiply by AREA, use:
"NETVALUE" > ("TAX" + "PRICE") * "AREA"
Building expressions with more than one criteria To search for several strings or values in a field, use the IN operator. For example, this expression will search for four different state names in a shared geodatabase:
STATE_NAME IN ('Alabama', 'Alaska', 'California', 'Florida')
To build an expression with more than one criteria when both criteria must be true, use the AND operator. For example, to find areas of pine forest larger than 100 square kilometers, use this expression:
VEGETATION = 'Pine Forest' AND AREA > 100
When at least one of the criteria must be true, use the OR operator. For example, this expression will select any feature with at least one of the following conditions:
SALES > 20000 OR ORDERS > 20000
To exclude, use the NOT operator. For example, to select all the Pacific states except California, use:
SUB_REGION = 'Pacific' AND NOT STATE_NAME = 'California'
|