SQL Query for string values with trailing space

Question

Hello,
I’m working with a hosted point feature layer used for field data collection. One of the attributes is a Project Name field (string). I use definition queries in ArcGIS Pro and a Dashboard in ArcGIS Online to check the values in this field to make sure they are consistent.

I’ve noticed that trailing spaces in the Project Name field are ignored by queries in ArcGIS Pro and Dashboards. For example, In ArcGIS Pro, a definition query for ProjectName = 'TestProject1' will still return features with values like "TestProject1 " or "TestProject1   ". The same happens in a chart in Dashboards, "TestProject1" and "TestProject1 " both display as if they are the same value.

Why are trailing spaces not treated as unique string values in ArcGIS Pro and ArcGIS Online?

What’s the best way to reliably identify features where the Project Name contains trailing spaces or other inconsistencies?

JoshuaBixby · Answer

This seems like a defect, can you provide some sample data in a file geodatabase that replicates this behavior?

DavidSolari · Answer

I can confirm queries have trailing whitespace normalized (not leading whitespace though, that's something) in Pro. I'd suggest making an Idea to get strict whitespace as an option in Pro and on published services, although long standing design decisions like this are rarely updated.

In the meantime, you can brute force strict whitespace by tacking this on to the end of your queries:

AND CHAR_LENGTH(my_field) = CHAR_LENGTH(TRIM(BOTH ' ' FROM my_field))

That's the functions for a file geodatabase, you may have to use different string length and string trim functions.