Python in Field Calculator generates different output when ran on shapefile vs. enterprise database feature class

[JesseMardis1]

We created an expression in field calculator that concatenates individual components of an address into a full address field.  When I fill the code block and hit Apply, the output is what I would expect.  When my coworker performs the same actions, his results have additional spaces and not in any recognizable pattern.  Randomly, there will be extra spaces before and after various bits of the address.  After a while of investigating the differences in our actions and environments, we realized that he is running the script against a feature class in an enterprise database while I was testing it on an exported shapefile of that feature class.  Aside from that, the code is the same, the tables are the same in terms of data type, precision, etc., and we are both using ArcGIS Pro.   The script works as intended on a shapefile but adds spaces when run on a feature class in a database. 

Example -

When I run the script on the shapefile, I get "1984 MONKEY DR BLDG A FL 2 UNIT 26"

When he runs the script on the feature class, he gets "1984   MONKEY DR   BLDG A FL 2 UNIT 26" with extra spaces

Does anyone know what could be causing this?  I've included a screenshot of the field calculator.

[jcarlson]

I would guess that something is being altered in the export to shapefile. Can you directly compare the features in the shapefile vs the database to see if there are differences? It's possible that there's an empty character in the field that isn't None, '', or ' ', but that it is somehow being removed when exporting to shapefile.

[JesseMardis1]

Good call.  I checked this and found that there were no rogue characters in those fields in the database and all the empty cells are truly null.  Oddly enough, I found that there were spaces in all of the empty cells of the shapefile.  Not sure why those would be there on export, but they are.  Presumably, they get appropriately skipped because of the None, '', ' ' bit of code.  The database feature class appears to have no extra characters, though.

[Gene_Sipes]

Maybe try adding strip() to each field and then building out your spaces?

Like so:

strip([field1]) + " " + strip([field2])

[JesseMardis1]

I just tried this.  It actually wanted the syntax to be like ([field1]).strip() but I gave it a shot.  It actually added more spaces than it did before.  164 Weller Ln became 164   Weller Ln.  Good idea all the same.  It's as if it is putting spaces in for the street number suffix, prefix direction, and prefix type no matter what.

[Gene_Sipes]

Oops my bad, that's what I meant. 

I'm wondering if the data has spaces in it at the SDE level? Then when you export out to shape, the spaces get removed. So when you run the script on your data in SDE, your join is adding additional spaces.

I would recommend either running the script on SDE without a space in your join, like this ''.join, or run a field calculation on each of the fields in your SDE that you are joining, and remove spaces with strip(). 

In other words I assume the data needs a little clean up. Maybe try running it on a subset of data in SDE that you know is clean? 

[JesseMardis1 · Accepted solution]

Thanks for your help...

The solution here was to export to a shapefile, add the full address field, run the field calculator on the shapefile, then reimport.  We never figured out why the database feature class seemed to be adding extra space.  It's not an efficient solution, but it is a solution.  

Only took 3 months to circle back to this but I wanted to advise anyone reading that the original issue persisted and we had to do an inefficient workaround.  A solution is a solution, I guess.