ArcGIS Online requires developers to use standardized SQL queries when querying hosted feature layers. This makes it easier for developers and apps to query hosted feature layers and helps prevent SQL injection attacks. All ArcGIS applications automatically support standard SQL queries.
Limitations of standardized queries
- Standardized queries are applied to your entire organization; they cannot be disabled for some layers.
- 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, Microsoft Azure SQL Database , are also not supported.
Which SQL functions are supported in ArcGIS Online?
If you're an app developer currently using database-specific where clause syntax, you'll need to update the where clauses in your app's code to use common SQL syntax supported by ArcGIS Online. The following tables show which SQL functions are supported and the syntax for each. When the following functions and syntax are used in your apps, ArcGIS Online converts them to conform to the specification of the database used by the feature layer.
Date functions
| Function | Description |
|---|---|
CURRENT_DATE() | Returns the current date in UTC time. What you see in the client depends on the client you're using. In ArcGIS Online, dates are shown in the time zone for your organization or profile. |
CURRENT_TIME() | Returns the current UTC date and time (hours, minutes, seconds). What you see in the client depends on the client you're using. In ArcGIS Online, time is displayed in the local time of your organization or profile. |
CURRENT_TIMESTAMP() | Returns the current UTC date and time (hours, minutes, seconds, milliseconds). What you see in the client depends on the client you're using. In ArcGIS Online, time is displayed in the local time of your organization or profile. |
EXTRACT(<unit> FROM <date>) | Returns a single part (<unit>) of the specified <date>. Possible <unit> values include but are not limited to year, month, day, hour, and minute. Examples
|
Numeric functions
| Function | Description |
|---|---|
ABS(<number>) | Returns the absolute (positive) value of the number you specify. |
CEILING(<number>) | Returns the smallest integer greater than or equal to the specified number. Example
|
COS(<number>) | Returns the trigonometric cosine of <number>, which is assumed to be an angle in radians. |
CAST(<number> AS FLOAT | INT) | Converts a number to a different type. FLOAT converts the specified number to a double and INT converts it to an integer. |
FLOOR(<number>) | Returns the largest integer that is less than or equal to the specified number. Example
|
LOG(<number>) | The natural logarithm of the specified number. |
LOG10(<number>) | The base-10 logarithm of the specified number. |
MOD(<number>, <n>) | Returns the remainder after the dividend (<number>) is divided by the divisor <n>. Both <n> and <number> must be of type integer. Examples include the following:
|
NULLIF(<number>, <value>) | Returns null if the specified number equals the specified value. NULLIF is commonly used to prevent divide-by-zero errors by setting <value> to 0. Whenever a calculation encounters a null field value in any of its arguments, the result of the calculation is null. For example, suppose you need to calculate a double field to be TOTALPOP divided by POP18. If any feature has a POP18 value that equals zero, the calculation will result in a divide-by-zero error. You could create a filter to hide records where POP18 is zero, and perform your calculation. A shortcut is to use NULLIF.
|
POWER(<number> , <y>) | Returns the value of the specified number raised to the specified power (<y>). |
ROUND(<number> , <length>) | Rounds the number you specify to the specified length. If you use a positive number for the <length>, the number is rounded to the decimal position specified by <length>. When <length> is a negative number, the specified <number> is rounded on the left side of the decimal point. Examples
|
SIN(<number>) | Returns the trigonometric sine of the specified <number>, which is assumed to be an angle in radians. |
TAN(<number>) | Returns the tangent of the specified <number>, which is assumed to be an angle in radians. |
TRUNCATE(<number>,<decimal_place>) | Truncates the <number> at the specified <decimal_place>. A positive <decimal_place> truncates to the decimal position specified. When <decimal_place> is a negative number, the <number> is truncated on the left side of the decimal point. Examples
|
String functions
| Function | Description |
|---|---|
CHAR_LENGTH(<string>) | Returns the number of characters in the specified string. The result is an integer. Example
|
CONCAT(<string1>, <string2>) | Concatenates two string values. Only two strings can be provided. To concatenate more than two strings, nest consecutive CONCAT functions as shown below. Examples
Null values are converted to an empty string. |
POSITION(<substring>, <string>) | Returns the position of the first occurrence of the specified substring in the string you specify. If the specified substring is not found, the result is 0. Examples
|
SUBSTRING(<string>, <start>, <length>) | Returns a part of a string value; <start> is an integer index specifying where the returned characters start, and <length> is the number of characters to be returned. Examples
|
TRIM(BOTH | LEADING | TRAILING ' ' FROM <string>) | Returns a string where all leading or trailing spaces are removed from the string you specify. Example
Note that the second argument is two single quotation marks with a space between them. |
UPPER(<string>) | Returns a string where all characters are converted to uppercase. Example
|
LOWER(<string>) | Returns a string where all characters are converted to lowercase. Example
|