Standardized SQL functions in ArcGIS Online

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

FunctionDescription

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

  • EXTRACT(MONTH FROM 12/21/2016)—returns 12.
  • EXTRACT(DAY FROM 12/21/2016 12:00)—returns 21.
  • EXTRACT(HOUR FROM 12/21/2016 15:00)—returns 15.

Numeric functions

FunctionDescription

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

  • CEILING(12.93)—result is 13.

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

  • FLOOR(12.93)—result is 12.

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:

  • MOD(10, 4)—result is 2.
  • MOD(CAST(DBLFIELD AS INT), 4)DBLFIELD is a field of type double, so the CAST function is needed to convert values from double to integer.

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.

  • TOTALPOP / NULLIF(POP18, 0)—returns null if POP18 is equal to zero; otherwise, the value of TOTALPOP / POP18 is returned.

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

  • ROUND(10.9934,2)—returns 10.99.
  • ROUND(10.9964,2)—returns 11.00.
  • ROUND(111.0,-2)—returns 100.00.

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

  • TRUNCATE(111.996,2)—returns 111.99.
  • TRUNCATE(111.996,-2)—returns 100.00.

String functions

FunctionDescription

CHAR_LENGTH(<string>)

Returns the number of characters in the specified string. The result is an integer.

Example

  • CHAR_LENGTH('Redlands')—result is 8.

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

  • CONCAT('A', 'B')—result is 'AB'.
  • CONCAT('A', CONCAT(':', 'B'))—result is 'A:B'.

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

  • POSITION('boat', 'Sailboat')—result is 5.
  • POSITION('motor', 'Sailboat')—result is 0.

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

  • SUBSTRING('Sailboat', 5, 4)—result is 'boat'.
  • SUBSTRING('Sailboat', 1, 4)—result is 'Sail'.
  • SUBSTRING('Sailboat', 5, 100)—result is 'boat'.

TRIM(BOTH | LEADING | TRAILING ' ' FROM <string>)

Returns a string where all leading or trailing spaces are removed from the string you specify.

Example

  • TRIM(BOTH ' ' FROM ' San Bernardino ')—result is 'San Bernardino'.

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

  • UPPER('Sailboat')—result is 'SAILBOAT'.

LOWER(<string>)

Returns a string where all characters are converted to lowercase.

Example

  • LOWER('Sailboat')—result is 'sailboat'.