arcpy - Selecting blank values within a table

I am selecting the following to remove NULL values from a selection, but unable to remove BLANK values from the selection. Would selecting BLANK values only be done by using a string length of 0? I do not want any field that is BLANK, NULL, or a email in my selection, because I am trying to move all fields with comments or websites into another field named comments.

arcpy.SelectLayerByAttribute_management("contacts", "NEW_SELECTION", "Contact_Email IS NOT NULL" )

Answer

Sometimes blank fields can be made up a number of different ways.

• The field could be NULL - it doesn't contain any value at all


• The field could be an empty string, but not NULL - ""


• The field could contain a space but nothing else - " "

You may need to allow for all these possibilities:

query = "Contact_Email IS NOT NULL and Contact_Email != '' and Contact_Email != ' '"
arcpy.SelectLayerByAttribute_management("contacts", "NEW_SELECTION", query)

That said, sometimes an easier way to find if a field contains an email address is to search for the @ symbol instead:

query = "Contact_Email LIKE '%@%'"

arcpy.SelectLayerByAttribute_management("contacts", "NEW_SELECTION", query)

---

If you are trying to select the blanks, you can reverse the query, but will need to include IS NULL:

query = "Contact_Email NOT LIKE '%@%' OR Contact_Email IS NULL"
arcpy.SelectLayerByAttribute_management("contacts", "NEW_SELECTION", query)

The screenshot below shows this query run in the ArcMap Python window, and the selection it produces

---

Update based on new information from comments.

To select records that don't have an email address and are not blank use the following query:

query = "Contact_Email NOT LIKE '%@%' and Contact_Email <> ''"
arcpy.SelectLayerByAttribute_management("contacts", "NEW_SELECTION", query)