Standardized SQL functions in ArcGIS Online

ArcGIS Online requires developers to use standardized SQL queries when querying hosted feature layers. Using standardized SQL queries helps prevent SQL injection attacks. All ArcGIS applications 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 not supported.

SQL functions that 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 value is displayed 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 value is displayed 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 value is displayed 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. The following examples extract different units from the date time value 2016-12-21 15:11: EXTRACT(MONTH FROM TIMESTAMP '2016-12-21 15:11:00')—returns 12. EXTRACT(DAY FROM TIMESTAMP '2016-12-21 15:11:00')—returns 21. EXTRACT(HOUR FROM TIMESTAMP '2016-12-21 15:11:00')—returns 15.

Numeric functions

Function	Description
ABS(<number>)	Returns the absolute (positive) value of the number you specify.
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. In the first example below, the number is cast to an integer. Because integers are whole numbers, the result is 1424. In the second example, an integer is cast to a float, which results in a number with decimal places, 1424.0 CAST(1424.49 AS INT) CAST(1424 AS FLOAT
CEILING(<number>)	Returns the smallest integer greater than or equal to the specified number. The following example returns 13: CEILING(12.93)
COS(<number>)	Returns the trigonometric cosine of <number>, which is assumed to be an angle in radians.
FLOOR(<number>)	Returns the largest integer that is less than or equal to the specified number. The following example returns 12: FLOOR(12.93)
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>). The following example returns 32768: POWER(8,5)
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 to the right of the decimal point. When <length> is a negative number, the specified <number> is rounded on the left side of the decimal point. Examples are as follows: 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. In the first example, the numbers to the right of the decimal place are truncated to include only two numbers, resulting in the value 111.99. In the second example, the numbers to the left of the decimal place are truncated, resulting in the value 100.00. TRUNCATE(111.996,2) TRUNCATE(111.996,-2)

String functions

Function	Description
CAST(<string> AS DATE | TIME)	Converts the string to a date or time if the string value is in a supported format. If the string is in the format 'MM/DD/YYYY hh:mm:ss' or 'YYYY-MM-DD', you can cast it to a date. If the string is in the format 'HH:MM:SS', you can cast it to the time only data type (TIME). For example, the follow string can be cast to a date: CAST('1988-05-30' AS DATE)
CHAR_LENGTH(<string>)	Returns the number of characters in the specified string. The result is an integer. For example, the following statement returns 8: CHAR_LENGTH('Redlands')
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. The first example below concatenates the letters A and B. The second example shows a nested CONCAT function to concatenate three string values, A, :, and B. CONCAT('A', 'B')—result is 'AB'. CONCAT('A', CONCAT(':', 'B'))—result is 'A:B'. Null values are converted to an empty string.
CURRENT_USER	When the CURRENT_USER function is included in a query, it acts similar to a variable; the username of the user accessing the hosted feature layer or hosted feature layer view is identified and used in the query. For example, if the user, planner3, is signed in to the organization to access a hosted feature layer view that contains the following query definition, only features where the staffmember field contains the value planner3 are returned to the connecting user: staffmember=current_user In the next example, multiple values are stored in the staffmember field. The following where clause will find the current user name in the text value in the staffmember field even if multiple usernames are stored in the field: where=position(current_user in staffmember)>0
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. In the first example below, the result is 5 because the first letter (b) of the substring (boat) is the fifth letter in the string (Sailboat). In the second example, the result is 0, because the substring (motor) is not present in the string. POSITION('boat', 'Sailboat') POSITION('motor', 'Sailboat')
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. See the following 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. In the following example, a space exists before and after the string, San Bernardino. The keyword BOTH is used to trim the space string (indicated using two single quotation marks with a space between them) from the start and end of the text string: TRIM(BOTH ' ' FROM ' San Bernardino ') This returns the string 'San Bernardino'.
UPPER(<string>)	Returns a string where all characters are converted to uppercase. In this example, all letters in the string Sailboat are converted to uppercase, resulting in the string 'SAILBOAT': UPPER('Sailboat')
LOWER(<string>)	Returns a string where all characters are converted to lowercase. In the next example, 'sailboat' is returned: LOWER('Sailboat')