SQL UPPER works in Filter function but TRIM doesn't

HollyTorpey_LSA

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! 

dougbrowning

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