SQL query 10.9 not working

[MatthewDriscoll]

According to the documentation as of 10.9, hosted feature services support SQL statements.  

https://developers.arcgis.com/rest/services-reference/enterprise/query-feature-service-layer-.html

• Hosted feature services on a relational data store support SQL expression for the outFields parameter when supportsOutFieldSqlExpression, under advancedQueryCapabilities, is true. Hosted feature services in ArcGIS Online already support this functionality.

https://developers.arcgis.com/javascript/latest/api-reference/esri-rest-support-Query.html

• If specifying outFields as expressions on a feature service-based FeatureLayer, the service capabilities advancedQueryCapabilities.supportsOutFieldSQLExpression and useStandardizedQueries must both be true.

But a very simply expression does not work.  PartyName LIKE '%driscoll%',

on this layer: https://www.jfksgis.us/server/rest/services/Hosted/Parcel_hosted/FeatureServer/0/

The SQL query works fine in Pro and for Online services: https://services1.arcgis.com/tpr1e2h7Rmn9C5oc/ArcGIS/rest/services/JFKS_GIS_ParcelOrion_Public_V4/Fe...

So is this not actually supported in server 10.9?

[jcarlson · Accepted solution]

Seems to work just fine, but keep in mind that these queries are two different things:

PartyName LIKE '%driscoll%'
PartyName LIKE '%Driscoll%'

Unfortunately, we can't use ILIKE to ignore the case of the input string. Using a bracketed set of characters, like "[Dd]riscoll" doesn't appear to work here either. I guess you'd have to combine two queries and put an OR between them.

[MatthewDriscoll]

Thanks.  This is a simplified problem, I have many attributes I need to query.  ESRI is logging it as an enhancement instead of a bug, even though it is a basic very simple sql statement, so it might be a long time until it gets fixed.  They need to update their documentation that  supportsOutFieldSQLExpression is not supported currently with rest endpoints.  

[jcarlson]

Personally, if I've got a lot of attributes to sift through, I just bring the entire field into a pandas dataframe and use regular expressions. It's much easier to define a really precise, adaptable regex expression than wrangle the SQL into submission.