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" )
| improve this question | | | | |
  • What happens when you run your selection? Are you saying that nothing is selected, or not everything is selected? – Midavalo Mar 27 '17 at 15:06
  • When I run my selection, only the NULL values are removed, not the blank rows. – Purplepeopleeater Mar 27 '17 at 15:08

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

enter image description here

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)

enter image description here

| improve this answer | | | | |
  • Comments are not for extended discussion; this conversation has been moved to chat. – Midavalo Mar 27 '17 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.