SQL reference for query expressions used in ArcGIS

This topic describes the elements of common queries used in selection expressions in ArcGIS. Query expressions in ArcGIS use common SQL syntax.

Caution:

SQL syntax does not work for calculating fields using Field Calculator.

Fields

To specify a field in an SQL expression, provide a delimiter if the field name would otherwise be ambiguous, such as if it were the same as an 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.

Field name delimiters differ from DBMS to DBMS. If you're querying any file-based data, such as a file geodatabase, ArcSDE geodatabase data, or data in an ArcIMS feature class or image service sublayer, you can enclose field names in double quotes:

"AREA"

If you're querying personal geodatabase data, you can enclose fields in square brackets:

[AREA]

For personal geodatabase raster datasets, you should enclose field names in double quotes:

"AREA"

For File geodatabase data you can enclose your field names in double quotes, but it's generally not needed.

AREA

Strings

Strings must always be enclosed in single quotes in queries. For example:

STATE_NAME = 'California'

• Strings are case sensitive in expressions. For feature classes and tables, you can use the UPPER or LOWER function to set the case for a selection. For example:

  UPPER(STATE_NAME) = 'RHODE ISLAND'

• Strings are case insensitive for personal geodatabase feature classes and tables. If needed, you can use the UCASE and LCASE functions that are equivalent to UPPER or LOWER.
• If the string contains a single quote you will first need to use another single quote as an escape character. For example:

  NAME = 'Alfie''s Trough'

The wildcards you use to conduct a partial string search also depend on the data source you are querying. For example, in a file-based or ArcSDE geodatabase data source, this expression would select Mississippi and Missouri among USA state names:

STATE_NAME LIKE 'Miss%'

The percent symbol (%) means that anything is acceptable in its place—one character, a hundred characters, or no character. The wildcards you use to query personal geodatabases are asterisk (*) for any number of characters and question mark (?) for one character.

String functions can be used to format strings. For instance, the LEFT function would return a certain number of characters starting on the left of the string. In this example, the query would return all states starting with the letter A:

LEFT(STATE_NAME,1) = 'A'

Refer to the documentation of your DBMS for a list of supported functions.

Numbers

The decimal point (.) is always used as the decimal delimiter, regardless of your locale or 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

Numeric functions can be used to format numbers. For instance, the ROUND function would round a number to a given number of decimals in a file geodatabase:

ROUND(SQKM,0) = 500

Refer to the documentation of your DBMS for a list of supported numeric functions.

Dates and time

General rules

Geodatabase data sources store dates in a date-time field. However, ArcInfo coverages and shapefiles do not.

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 if the field is known to contain only dates; in other cases, it needs to be stated, or the query will return a syntax error.

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 date-time field, but it is not recommended. Querying against time is a bit awkward; for instance, 12:30:05 p.m. will be stored as '1899-12-30 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.

The purpose of this section is only to help you query against dates, not time values. When a nonnull time is stored with the dates (for instance, January 12, 1999, 04:00:00), querying against the date only will not return the record because when you pass only a date to a date-time field, it will fill the time with zeros and retrieve only the records where the time is 12:00:00 a.m.

The attribute table shows date and time in a user-friendly format, depending on your regional settings, rather than the underlying database's format. This is fine most of the time but also has a few drawbacks:

• The string shown in the SQL query may only slightly resemble the value shown in the table, especially when time is involved. For instance, a time entered as 00:00:15 will show as 12:00:15 a.m. in the attribute table, with the United States as your regional settings, and the comparable query syntax would be Datefield = '1899-12-30 00:00:15'.
• The attribute table does not know what the underlying data source is until you save your edits. It will first try to format the value entered to fit its own format, then upon saving edits, it will try to tweak the resulting value to fit into the database. Because of this, you can enter a time in a shapefile, but you will find that it is dropped when you save your edits. The field will then contain a value '1899-12-30' that will show as 12:00:00 a.m. or something equivalent depending on your regional settings.

Date-time syntax for ArcSDE geodatabases

Informix

Datefield = 'yyyy-mm-dd hh:mm:ss'

The hh:mm:ss part of the query cannot be omitted even if it's equal to 00:00:00.

Oracle

Datefield = date 'yyyy-mm-dd'

Keep in mind this will not return records where the time is not null.

An alternative format for querying dates in Oracle follows:

Datefield = TO_DATE('yyyy-mm-dd hh:mm:ss','YYYY-MM-DD HH24:MI:SS')

The second parameter 'YYYY-MM-DD HH24:MI:SS' describes the format used for querying. An actual query would look like this:

Datefield = TO_DATE('2003-01-08 14:35:00','YYYY-MM-DD HH24:MI:SS')

You can use a shorter version:

TO_DATE('2003-11-18','YYYY-MM-DD')

Again, this will not return records where the time is not null.

SQL Server

Datefield = 'yyyy-mm-dd hh:mm:ss'

The hh:mm:ss part of the query can be omitted when the time is not set in the records.

An alternative format is this:

Datefield = 'mm/dd/yyyy'

IBM DB2

Datefield = TO_DATE('yyyy-mm-dd hh:mm:ss','YYYY-MM-DD HH24:MI:SS')

The hh:mm:ss part of the query cannot be omitted even if the time is equal to 00:00:00.

PostgreSQL

Datefield = TIMESTAMP 'YYYY-MM-DD HH24:MI:SS'
Datefield = TIMESTAMP 'YYYY-MM-DD'

You must specify the full time stamp when using "equal to" queries, or else no records will be returned. You could successfully query with the following statements if the table you query contains date records with these exact time stamps (2007-05-29 00:00:00 or 2007-05-29 12:14:25):

select * from table where date = '2007-05-29 00:00:00';

or

select * from table where date = '2007-05-29 12:14:25';

If you use other operators, such as greater than, less than, greater than or equal to, or less than or equal to, you don't have to designate the time, although you can if you want to be that precise. Both of the following statements would work:

select * from table where date < '2007-05-29';

select * from table where date < '2007-05-29 12:14:25';

File geodatabases, shapefiles, coverages, and other file-based data sources

Dates in file geodatabases, shapefiles, and coverages are preceded with date.

"Datefield" = date 'yyyy-mm-dd'

File geodatabases support the use of a time in the date field, so this can be added to the expression:

"Datefield" = date 'yyyy-mm-dd hh:mm:ss'

Shapefiles and coverages do not support the use of time in a date field.

Note:

All SQL used by the file geodatabase is based on the SQL-92 standard.

Personal geodatabase

Dates in personal geodatabases are delimited using a pound sign (#).

For example:

[Datefield] = #mm-dd-yyyy hh:mm:ss#

It can be shortened to [Datefield] = #mm-dd-yyyy#.

An alternative format

[Datefield] = #yyyy/mm/dd#

Known limitations

Querying against a date on the left part (first table) of a join only works with file-based data sources, such as file geodatabases, shapefiles, and DBF tables. However, there is a possible workaround for working with non-file-based data, like personal geodatabase data and ArcSDE data as described below.

Querying against a date on the left part of a join will be successful when using the limited version of SQL developed for file-based data sources. If you are not using such a data source, you can force the expression to use this format. This can be done by making sure that the query expression involves fields from more than one join table. For example, if a feature class and a table (FC1 and Table1) are joined and are both from a personal geodatabase, the following expressions will fail or return no data:

FC1.date = date #01/12/2001#
FC1.date = date '01/12/2001'

To query successfully, you can create a query as follows:

FC1.date = date '01/12/2001' and Table1.OBJECTID > 0

Since the query involves fields from both tables, the limited SQL version will be used. In this expression, Table1.OBJECTID is always > 0 for records that matched during join creation, so this expression is true for all rows that contain join matches.

To ensure that every record with FC1.date = date '01/12/2001' is selected, use the following query:

FC1.date = date '01/12/2001' and (Table1.OBJECTID IS NOT NULL OR Table1.OBJECTID IS NULL)

This query will select all records with FC1.date = date '01/12/2001', whether or not there was a join match for each particular record.

Subqueries

Note:

Coverages, shapefiles, and other nongeodatabase file-based data sources do not support subqueries. Subqueries that are performed on versioned ArcSDE feature classes and tables will not return features that are 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.

A subquery is a query nested within another query. It can be used to apply predicate or aggregate functions or to compare data with values stored in another table. This can be done with the IN or ANY keyword. For example, this query would select only the countries that are not also listed in the table indep_countries:

"COUNTRY_NAME" NOT IN (SELECT "COUNTRY_NAME" FROM indep_countries)

This query would return the features with a GDP2006 greater than the GDP2005 of any of the features contained in countries:

"GDP2006" > (SELECT MAX("GDP2005") FROM countries)

For each record in the table, a subquery may need to parse all the data in its target table. It may be extremely slow to execute on a large dataset.

Subquery support in file geodatabases is limited to the following:

• IN predicate. For example:

  "COUNTRY_NAME" NOT IN (SELECT "COUNTRY_NAME" FROM indep_countries)

• Scalar subqueries with comparison operators. A scalar subquery returns a single value. For example:

  "GDP2006" > (SELECT MAX("GDP2005") FROM countries)

  For file geodatabases, the set functions AVG, COUNT, MIN, MAX, and SUM can only be used within scalar subqueries.
• EXISTS predicate. For example:

  EXISTS (SELECT * FROM indep_countries WHERE "COUNTRY_NAME" = 'Mexico')

Operators

The following is the full list of query operators supported by file geodatabases, shapefiles, coverages, and other file-based 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.

Arithmetic operators

Operator	Description
*	Arithmetic operator for multiplication
/	Arithmetic operator for division
+	Arithmetic operator for addition
-	Arithmetic operator for subtraction

Comparison operators

You use comparison operators to compare one expression to another.

Comparison operators

Operator	Description
<	Less than. It can be used with strings (comparison is based on alphabetical order), numbers, and dates.
<=	Less than or equal to. It can be used with strings (comparison is based on alphabetical order), numbers, and dates.
<>	Not equal to. It can be used with strings (comparison is based on alphabetical order), numbers, and dates.
>	Greater than. It can be used with strings (comparison is based on alphabetical order), numbers, and dates.
>=	Greater than or equal to. It 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 'escape-character']	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%' The percent symbol (%) 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 an underscore (_). For example, this expression finds Catherine Smith and Katherine Smith: "OWNER_NAME" LIKE '_atherine Smith' The percent symbol and underscore wildcards work for any file-based 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 the percent symbol or underscore 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 asterisk (*) for any number of characters and question mark (?) for one character. The pound sign (#) 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#'

Logical operators

Logical operators

Operator	Description
AND	Combines two conditions together and 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 and 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 expression. For example, the following expression selects all states but California: NOT "STATE_NAME" = 'California'

String Operators

String operators

Operator	Description
||	Returns a character string that is the result of concatenating two or more string expressions together. FIRST_NAME || MIDDLE_NAME || LAST_NAME

Functions

The following is the full list of functions supported by file geodatabases, shapefiles, coverages, and other file-based 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. See your DBMS documentation for details.

Date functions

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 date-time expression. The extract_field argument can be one of the following keywords: YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND.
CURRENT TIME	Returns the current time.

String functions

Arguments denoted asstring_exp can be the name of a column, a character-string-literal, or the result of another scalar function, where the underlying data type can be represented as a character type.

Arguments denoted ascharacter_exp are variable-length character strings.

Arguments denoted asstart or length can be a numeric-literal or the result of another scalar function, where the underlying data type can be represented as a numeric type.

These string functions are 1-based; that is, the first character in the string is character 1.

String functions

Function	Description
CHAR_LENGTH(string_exp)	Returns the length in characters of the string expression.
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 implementation-defined 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.

Numeric functions

All numeric functions return a numeric value.

Arguments denoted as numeric_exp, float_exp, or integer_exp can be the name of a column, the result of another scalar function, or a numeric-literal, where the underlying data type could be represented as a numeric type.

Numeric functions

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.

The CAST function

The CAST function converts a value to a specified data type. The syntax is as follows:

CAST(exp AS data_type)

The argument exp can be the name of a column, the result of another scalar function, or a literal. Data_type can be any of the following keywords, which can be specified in upper- or lowercase: CHAR, VARCHAR, INTEGER, SMALLINT, REAL, DOUBLE, DATE, TIME, DATETIME, NUMERIC, or DECIMAL.

For more information on the CAST function, see CAST and CONVERT.