Standardized SQL functions in ArcGIS Online
ArcGIS Online includes a security option that forces developers to use standardized SQL queries when working with feature services hosted on ArcGIS Online. This makes it easier for developers and applications to query hosted feature services and helps prevent SQL injection attacks. Standardized queries are not enforced by default, but can be enabled by the organization administrator.
Limitations of standardized queries
Standardized queries are applied to your entire organization; they cannot be enabled for some services and disabled for others.
Standardized queries are not supported on joins between different workspaces.
Subqueries as a where clause, for example, POP_2010 = (SELECT min(POP_2010) FROM counties, are not supported.
Queries from database services, for example, Windows Azure SQL Data, are also not supported.
Which SQL functions are supported in ArcGIS Online?
If you're an application developer currently using database-specific where clause syntax, you'll need to update the where clauses in your application's code to use common SQL syntax supported by ArcGIS Online. The following list shows which SQL functions are supported and the syntax for each. When the following functions and syntax are used in your applications, ArcGIS Online converts them to conform to the specification of the database used by the service.
Function type | Function | Description | Example | Notes |
|---|---|---|---|---|
Date | CURRENT_DATE | Returns the current date in the session time zone. | Datefield < CURRENT_DATE | Only the following date and timestamp syntax is supported: date 'yyyy-mm-dd', for example, Datefield = date '2012-05-29' timestamp 'yyyy-mm-dd hh:mm:ss', for example, Datefield = timestamp '2012-05-29 15:14:25' |
CURRENT_TIMESTAMP | Returns the current local time. | Timestampfield < CURRENT_TIMESTAMP | ||
EXTRACTEXTRACT(extract_field FROM extract_source) | Returns a single part of the date/time, such as year, month, day, hour, minute, and so on. The extract_field argument can be one of the following keywords: YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND. | Search all rows from the month of November: EXTRACT(MONTH FROM Datefield) = 11 | ||
Math | ABS(numeric_exp) | Returns the absolute (positive) value of the specified numeric expression. | ||
CEILING(numeric_exp) | Returns the smallest integer greater than, or equal to, the specified numeric expression. | |||
FLOOR(numeric_exp) | Returns the largest integer less than or equal to the specified numeric expression. | |||
LOG(float_exp) | Returns the natural logarithm of the specified float expression. | |||
LOG10(float_exp) | Returns the base-10 logarithm of the specified float expression. | |||
POWER(numeric_exp, integer_exp) | Returns the value of the specified expression to the specified power. | POWER(Numericfield, 2) = 16 | ||
ROUND(numeric_exp, integer_exp) | Returns a numeric value, rounded to the specified length or precision. | |||
TRUNCATE(numeric_exp, integer_exp) | Returns a numeric value without rounding the value to a specific length or precision. | |||
String | CHAR_LENGTH(string_exp) | Returns the length in characters of the input string. | ||
CONCAT(string_exp1, string_exp2) | Returns a string that is the result of concatenating two or more string values. | |||
LOWER(string_exp) | Returns a character expression after converting uppercase character data to lowercase. | |||
SUBSTRING(string_exp FROM start FOR length) | Returns part of a character or text expression. | Search all rows where the first two characters from values in Stringfield are Ch: SUBSTRING(Stringfield FROM 1 FOR 2)='Ch' | ||
UPPER(string_exp) | Returns a character expression with lowercase character data converted to uppercase. |