Check for nulls that aren't null and fix them with an actual null

[steveoh]

Create a sweeper that replaces

• "" empty string -> null
• " " empty spaces -> null
• "null" null string (ignoring case) -> null
• "" null string with brackets (ignoring case) -> null

What other values does the data team find in real world data?

empty, -, ...?

[stdavis]

I can see translating the null strings to native null values, but converting empty strings to be null seems like it may be a bad idea. I feel like there might be cases where having both empty strings and null values are important.

[steveoh]

I struggle to see the case where an empty string is important. Would it be web map labeling or a where clause?

[ZachBeck]

I think @gregbunce has code that can't handle nulls in certain fields in the address points. I imagine the code could be changed though...

[gregbunce]

I think you're right. It might be the UT unique ID field. I can work around it. It will cause some scripts to break but it can be worked out. In general I'd like to have nulls over empty stings in all the data, though. I think that conforms more to the standard and is easier when running queries.

[stdavis]

I'm not a data guy anymore so take it for what it's worth. But I remember there being a difference between empty (null) and intentionally set as blank ('').

[steveoh]

I'm not a data guy anymore so take it for what it's worth. But I remember there being a difference between empty (null) and intentionally set as blank ('').

Is that like null vs undefined?

[gregbunce]

yeah, i go the extra mile each month to convert all the nulls in about 10 different fields to empty string "". it's a pain, but I was told downstream scripts and processes depend on there being empty stings in there (and no nulls). again, nulls would make my life easier, but I don't fully understand the downstream processes dependent on empty strings. I suppose that's step one.

[steveoh]

It seems more straight forward in scripts to test for null. I do remember arcmap being weird about it depending on the data source.

In a definition query field is NULL is correct right?

In scripts it would be

#: yada yada

string_field, null_field = cursor.next()

if null_field is None:
  #: field is null do something
  pass

if not (string_field and string_field.strip()):
  #: string is null empty or blank, do something
  pass

#: yada yada

I realize we don't live in a perfect world and there might be some heartburn with this. Similar to the domain code issue. But do we agree that it is the right™ thing to do? Or does the null vs undefined argument mean that empty strings should be kept as empty strings but other values can be converted to a real null?

[stdavis]

Is that like null vs undefined?

Yes, I view it as the same situation.

Or does the null vs undefined argument mean that empty strings should be kept as empty strings but other values can be converted to a real null?

This would be my vote. But again, I'm not a data guy and it doesn't affect me either way.