SQL UPPER works in Filter function but TRIM doesn't

HollyTorpey_LSA — Thu, 21 Dec 2023 20:45:48 GMT

I'm working on an Arcade field calculation in Field Maps. I'm working in the Map Viewer.

This works:

var projectSoilPits = filter($featureset, "UPPER(ProjectNumber) = @projectNumber")

This doesn't :

var projectSoilPits = filter($featureset, "TRIM(ProjectNumber) = @projectNumber")

Neither does this:

var projectSoilPits = filter($featureset, "TRIM(both ' ' from ProjectNumber) = @projectNumber")

Both result in a "Failed to calculate" error when I test the form in both the Map Viewer and the mobile app.

Can someone please tell me why the SQL-92 UPPER function is working for me but TRIM isn't? Thanks in advance!

Re: SQL UPPER works in Filter function but TRIM doesn't

DougBrowning — Fri, 22 Dec 2023 14:37:48 GMT

It is probably that SQL is not fully implemented in all tools. Plus what is implemented varies between, SDE, GDB, in memory sets, AGOL, Map Viewer and ArcPro/ArcMap.

I have asked a few times over the years for documentation on any of this but never got a response. Makes it hard when you are just guessing. Sometimes the syntax is also different. Esp true with in_memory and memory in Python. My guess is that Arcade is also not full SQL

Just some of the posts on this. Sorry not a great answer but it may be better to look for another way like contains maybe?

https://resources.arcgis.com/en/help/main/10.2/index.html#/SQL_reference_for_query_expressions_used_in_ArcGIS/00s500000033000000/

Many posts on this

https://community.esri.com/t5/python-questions/cast-does-not-work-in-searchcursor-queries/m-p/264322

https://community.esri.com/t5/arcgis-online-questions/new-map-viewer-and-classic-cannot-handle/m-p/1107089

https://community.esri.com/t5/data-management-questions/using-sql-cast-functionality-in-against-a-file/m-p/209575

https://community.esri.com/t5/arcgis-pro-questions/are-def-query-using-sql-now-different/m-p/85499

https://community.esri.com/t5/python-questions/sql-clause-in-arcpy-da-searchcursor-is-not/m-p/51603

https://community.esri.com/t5/geodatabase-questions/what-sql-commands-do-in-memory-workspaces-support/m-p/862454

topic SQL UPPER works in Filter function but TRIM doesn't in ArcGIS Field Maps Questions

SQL UPPER works in Filter function but TRIM doesn't

Re: SQL UPPER works in Filter function but TRIM doesn't