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. Therefore, most of the query syntax listed below contains a reference to the time. However, ArcInfo coverages and shapefiles do not. Geodatabase data sources store dates in a date-time field. Refer to the documentation of your DBMS for a list of supported numeric functions. For instance, the ROUND function would round a number to a given number of decimals in a file geodatabase: ROUND(SQKM,0) = 500 Numeric functions can be used to format numbers.

You can query numbers using the equal (=), not equal (), greater than (>), less than (=), less than or equal to (= 5000 The comma cannot be used as a decimal or thousands delimiter in an expression. The decimal point (.) is always used as the decimal delimiter, regardless of your locale or regional settings. Refer to the documentation of your DBMS for a list of supported functions. In this example, the query would return all states starting with the letter A: LEFT(STATE_NAME,1) = 'A' For instance, the LEFT function would return a certain number of characters starting on the left of the string. String functions can be used to format strings. The wildcards you use to query personal geodatabases are asterisk (*) for any number of characters and question mark (?) for one character. The percent symbol (%) means that anything is acceptable in its place-one character, a hundred characters, or no character. 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 wildcards you use to conduct a partial string search also depend on the data source you are querying.

If the string contains a single quote you will first need to use another single quote as an escape character.

If needed, you can use the UCASE and LCASE functions that are equivalent to UPPER or LOWER.

Strings are case insensitive for personal geodatabase feature classes and tables.

For example: UPPER(STATE_NAME) = 'RHODE ISLAND' For feature classes and tables, you can use the UPPER or LOWER function to set the case for a selection.

Strings are case sensitive in expressions.

Strings must always be enclosed in single quotes in queries. If you're querying personal geodatabase data, you can enclose fields in square brackets: ДЇor personal geodatabase raster datasets, you should enclose field names in double quotes: 'AREA'ДЇor File geodatabase data you can enclose your field names in double quotes, but it's generally not needed. 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' 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.ДЇield name delimiters differ from DBMS to DBMS.

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.

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

Devonthink pro and sql query