SQL reference 

Release 9.3
Last modified April 24, 2009 
Print all topics in : "Selecting features and graphics" 
Note:
This topic was updated for 9.3.1.
This topic is a reference to the SQL (Structured Query Language) WHERE clause features and functions in ArcGIS and applies to file, personal, and ArcSDE geodatabases as well as filebased data sources such as shapefiles and coverages. It shows you how SQL syntax differs depending on the data source. Migrating to the file geodatabase contains a concise summary of the syntax differences between file and personal geodatabases.
File geodatabases and other filebased data sources support the WHERE clause features and functions listed in this topic only. Personal and ArcSDE geodatabases support many additional features and functions not mentioned in this topic. For information on those, refer to your database management system (DBMS) documentation.
To specify a field in a SQL expression, you only need to provide a delimiter if the field name would otherwise be ambiguous, such as if it were the same as a SQL reserved keyword. Since there are many reserved keywords and new ones can be added in subsequent releases, a good practice is to always enclose a field name with a delimiter.
If you're querying any filebased data such as a file geodatabase, ArcSDE geodatabase data, or data in an ArcIMS feature class or ArcIMS image service sublayer, you can enclose field names in double quotes:
"AREA"
[AREA]
Strings must always be enclosed in single quotes in queries. For example:
STATE_NAME = 'California'
UPPER("STATE_NAME") = 'RHODE ISLAND'
LEFT("STATE_NAME",1) = 'A'
The decimal point (.) is always used as the decimal delimiter, regardless of your regional settings. The comma cannot be used as a decimal or thousands delimiter in an expression.
You can query numbers using the equal (=), not equal (<>), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and BETWEEN operators. For example:
"POPULATION" >= 5000
ROUND("SQKM",0) = 500
General rules
Most of the data sources, except coverages and shapefiles, store dates in a datetime field supporting the storage of both date and time information. Therefore, most of the query syntax listed below contains a reference to the time. In some cases, the time part of the query may be safely omitted when the field is known to contain only dates; in other cases, it needs to be stated or the query will return a syntax error. This is mentioned for each data source below.
The main purpose of the ArcMap date format is to store dates, not times. It is possible to store only a time in the field when the underlying database actually uses a datetime field, but it is not recommended. Querying against time is a bit awkward; for instance, 12:30:05 p.m. will be stored as '18991230 12:30:05'.
NOTE: Dates are stored in the underlying database as a reference to December 30, 1899, at 00:00:00. This is valid for all the data sources listed here.
Datefield = 'yyyymmdd hh:mm:ss'
Datefield = date 'yyyymmdd'
Datefield = TO_DATE('yyyymmdd hh:mm:ss','YYYYMMDD HH24:MI:SS')
Datefield = TO_DATE('20030108 14:35:00','YYYYMMDD HH24:MI:SS')
TO_DATE('20031118','YYYYMMDD')
Datefield = 'yyyymmdd hh:mm:ss'
Datefield = 'mm/dd/yyyy'
Datefield = TO_DATE('yyyymmdd hh:mm:ss','YYYYMMDD HH24:MI:SS')
Datefield = TIMESTAMP 'YYYYMMDD HH24:MI:SS' Datefield = TIMESTAMP 'YYYYMMDD'
select * from table where date = '20070529 00:00:00';
select * from table where date = '20070529 12:14:25';
select * from table where date < '20070529';
select * from table where date < '20070529 12:14:25';
"Datefield" = date 'yyyymmdd'
"Datefield" = date 'yyyymmdd hh:mm:ss'
[Datefield] = #mmddyyyy hh:mm:ss#
[Datefield] = #yyyy/mm/dd#
FC1.date = date #01/12/2001# FC1.date = date '01/12/2001'
FC1.date = date '01/12/2001' and Table1.OBJECTID >0
NOTE: Coverages, shapefiles, and other nongeodatabase filebased data sources do not support subqueries. Subqueries done on a versioned ArcSDE feature class that has been registered without the option to move edits to base will not return features stored in the delta tables. File geodatabases provide the limited support for subqueries explained in this section, while personal and ArcSDE geodatabases provide full support. For information on the full set of subquery capabilities of personal and ArcSDE geodatabases, refer to your DBMS documentation.
"COUNTRY_NAME" NOT IN (SELECT "COUNTRY_NAME" FROM indep_countries)
"GDP2006" > (SELECT MAX("GDP2005") FROM countries)
"COUNTRY_NAME" NOT IN (SELECT "COUNTRY_NAME" FROM indep_countries)
"GDP2006" > (SELECT MAX("GDP2005") FROM countries)
EXISTS (SELECT * FROM indep_countries WHERE "COUNTRY_NAME" = 'Mexico')
The following is the full list of operators supported by file geodatabases, shapefiles, coverages, and other filebased data sources. They are also supported by personal and ArcSDE geodatabases, although these data sources may require different syntax. In addition to the operators below, personal and ArcSDE geodatabases support additional capabilities. Please see your DBMS documentation for details.
Arithmetic operators
You use an arithmetic operator to add, subtract, multiply, and divide numeric values.
Operator  Description 
*  Arithmetic operator for multiplication 
/  Arithmetic operator for division 
+  Arithmetic operator for addition 
  Arithmetic operator for subtraction 
Operator  Description 
<  Less than. Can be used with strings (comparison is based on alphabetical order), numbers, and dates. 
<=  Less than or equal to. Can be used with strings (comparison is based on alphabetical order), numbers, and dates. 
<>  Not equal to. Can be used with strings (comparison is based on alphabetical order), numbers, and dates. 
>  Greater than. Can be used with strings (comparison is based on alphabetical order), numbers, and dates. 
>=  Greater than or equal to. Can be used with strings (comparison is based on alphabetical order), numbers, and dates. For example, this query selects all the cities with names starting with the letters M to Z:
"CITY_NAME" >= 'M' 
[NOT] BETWEEN x AND y  Selects a record if it has a value greater than or equal to x and less than or equal to y. When preceded by NOT, it selects a record if it has a value outside the specified range. For example, this expression selects all records with a value greater than or equal to 1 and less than or equal to 10:
"OBJECTID" BETWEEN 1 AND 10 This is the equivalent of the following expression: "OBJECTID" >= 1 AND OBJECTID <= 10 However, the expression with BETWEEN provides better performance if you're querying an indexed field. 
[NOT] EXISTS  Returns TRUE if the subquery returns at least one record; otherwise, it returns FALSE. For example, this expression returns TRUE if the OBJECTID field contains a value of 50:
EXISTS (SELECT * FROM parcels WHERE "OBJECTID" = 50) EXISTS is supported in file, personal, and ArcSDE geodatabases only. 
[NOT] IN  Selects a record if it has one of several strings or values in a field. When preceded by NOT, it selects a record if it doesn't have one of several strings or values in a field. For example, this expression searches for four different state names:
"STATE_NAME" IN ('Alabama', 'Alaska', 'California', 'Florida') For file, personal, and ArcSDE geodatabases, this operator can also be applied to a subquery: "STATE_NAME" IN (SELECT "STATE_NAME" FROM states WHERE "POP" > 5000000) 
IS [NOT] NULL  Selects a record if it has a null value for the specified field. When NULL is preceded by NOT, it selects a record if it has any value for the specified field. For example, this expression selects all records with a null value for population:
"POPULATION" IS NULL 
x [NOT] LIKE y [ESCAPE 'escapecharacter']  Use the LIKE operator (instead of the = operator) with wildcards to build a partial string search. For example, this expression selects Mississippi and Missouri among USA state names:
"STATE_NAME" LIKE 'Miss%' % 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 finds Catherine Smith and Katherine Smith: "OWNER_NAME" LIKE '_atherine Smith' The % and _ wildcards work for any filebased data or multiuser geodatabase data. LIKE works with character data on both sides of the expression. If you need to access noncharacter data, use the CAST function. For example, this query returns numbers that begin with 8 from the integer field SCORE_INT: CAST ("SCORE_INT" AS VARCHAR) LIKE '8%' To include % or _ in your search string, use the ESCAPE keyword to designate another character as the escape character, which in turn indicates that a real percent sign or underscore immediately follows. For example, this expression returns any string containing 10% such as 10% DISCOUNT or A10%: "AMOUNT" LIKE '%10$%%' ESCAPE '$' The wildcards you use to query personal geodatabases are * for any number of characters and ? for one character. The # is also used as a wildcard to match a single digit (numeric value). For example, this query returns parcel numbers A1, A2, and so on, from a personal geodatabase: [PARCEL_NUMBER] LIKE 'A#' 
Operator  Description 
AND  Combines two conditions together. Selects a record if both conditions are true. For example, the following expression selects any house with more than 1,500 square feet and a garage for more than two cars:
"AREA" > 1500 AND "GARAGE" > 2 
OR  Combines two conditions together. Selects a record if at least one condition is true. For example, the following expression selects any house with more than 1,500 square feet or a garage for more than two cars:
"AREA" > 1500 OR "GARAGE" > 2 
NOT  Selects a record if it doesn't match the following expression. For example, the following expression selects all states but California.
NOT "STATE_NAME" = 'California' 
The following is the full list of functions supported by file geodatabases, shapefiles, coverages, and other filebased data sources. They are also supported by personal and ArcSDE geodatabases, although these data sources may require different syntax or function name. In addition to the functions below, personal and ArcSDE geodatabases support additional capabilities. Please see your DBMS documentation for details.
Date functions
Function  Description 
CURRENT_DATE  Returns the current date. 
EXTRACT(extract_field FROM extract_source)  Returns the extract_field portion of the extract_source. The extract_source argument is a datetime expression. The extract_field argument can be one of the following keywords: YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND. 
Function  Description 
CHAR_LENGTH(string_exp)  Returns the length in characters of the string expression. 
CONCAT(string_exp1, string_exp2)  Returns a character string that is the result of concatenating string_exp2 to string_exp1. 
LOWER(string_exp)  Returns a string equal to that in string_exp, with all uppercase characters converted to lowercase. 
POSITION(character_exp IN character_exp)  Returns the position of the first character expression in the second character expression. The result is an exact numeric with an implementationdefined precision and a scale of zero. 
SUBSTRING(string_exp FROM start FOR length)  Returns a character string that is derived from string_exp, beginning at the character position specified by start for length characters. 
TRIM(BOTH  LEADING  TRAILING trim_character FROM string_exp)  Returns the string_exp with the trim_character removed from the leading, trailing, or both ends of the string. 
UPPER(string_exp)  Returns a string equal to that in string_exp, with all lowercase characters converted to uppercase. 
Function  Description 
ABS(numeric_exp)  Returns the absolute value of numeric_exp. 
ACOS(float_exp)  Returns the arccosine of float_exp as an angle, expressed in radians. 
ASIN(float_exp)  Returns the arcsine of float_exp as an angle, expressed in radians. 
ATAN(float_exp)  Returns the arctangent of float_exp as an angle, expressed in radians. 
CEILING(numeric_exp)  Returns the smallest integer greater than or equal to numeric_exp. 
COS(float_exp)  Returns the cosine of float_exp, where float_exp is an angle expressed in radians. 
FLOOR(numeric_exp)  Returns the largest integer less than or equal to numeric_exp. 
LOG(float_exp)  Returns the natural logarithm of float_exp. 
LOG10(float_exp)  Returns the base 10 logarithm of float_exp. 
MOD(integer_exp1, integer_exp2)  Returns the remainder of integer_exp1 divided by integer_exp2. 
POWER(numeric_exp, integer_exp)  Returns the value of numeric_exp to the power of integer_exp. 
ROUND(numeric_exp, integer_exp)  Returns numeric_exp rounded to integer_exp places to the right of the decimal point. If integer_exp is negative, numeric_exp is rounded to integer_exp places to the left of the decimal point. 
SIGN(numeric_exp)  Returns an indicator of the sign of numeric_exp. If numeric_exp is less than zero, 1 is returned. If numeric_exp equals zero, 0 is returned. If numeric_exp is greater than zero, 1 is returned. 
SIN(float_exp)  Returns the sine of float_exp, where float_exp is an angle expressed in radians. 
TAN(float_exp)  Returns the tangent of float_exp, where float_exp is an angle expressed in radians. 
TRUNCATE(numeric_exp, integer_exp)  Returns numeric_exp truncated to integer_exp places to the right of the decimal point. If integer_exp is negative, numeric_exp is truncated to integer_exp places to the left of the decimal point. 