ArcMap: Dealing with NULLs

NULL values in GIS data can trip you up if you don’t know how to deal with them.  A NULL value is not the same as a blank (empty) value, and actually takes up 1 bit more of space than an empty value.  Allowing a field to be NULL introduces an additional state that you would not have if the field was created to not allow NULLs.  And yes, you can have NULLs in numeric fields too!

Why have a NULL value in your data?  Usually this is used to mean “not set” or “uninitialized”.  A good example would be an employee database with a termination date field that is set to NULL when the employee is new.  Another example would be an elevation field.  You could use a NULL value to represent “not known”, since zero is a valid elevation.  More than likely you have fields that allow NULLs because someone created them not paying attention to the defaults.

So how do you specify a field to allow NULLs or not?  Open ArcMap (I’m using version 10.2.2) and bring in some data from a geodatabase.  Then open the data’s attribute table and select “Add Field…” from the Table Options icon.

nulls1

Note by default in the Field Properties that “Allow NULL Values” is set to “Yes”.  If you do not want NULLs, set it to “No”.  If you set it to “No” and do not specify a “Default Value”, then Text fields will default to a blank (empty) value and numeric fields will default to zero.  Also, keep in mind that you can add a field with “Allow NULL Values” set to “No” only if your feature class or table is not populated yet with records.

So let’s take a look at some test data I created with some fields that allow NULLs.

nulls2

As you can probably guess, the field TFIELD is a text field and NFIELD is a numeric field.  Note some records have NULL values while others have data.

Using the Select By Attributes tool, I can select NULL values in the TFIELD by constructing the following SQL expression.

nulls3

The records with NULL in the TFIELD are selected.

nulls4

If I wanted to select everything but NULL, I would have used this SQL expression.

nulls5

nulls6

Numeric fields work the same way when selecting NULLs.

nulls7

nulls8

Now let’s take a few records and calc the TFIELD value to blank.  First I select a few records.

nulls9

Then right click on the TFIELD name in the attribute table and select “Field Calculator…”.  Then in Field Calculator I specify TFIELD = “” (that is two double quotes with no space between them).

nulls10

Note the values change from NULL to blank.

nulls11

Now let’s select NULL values in TFIELD again with the Select By Attributes tool using “TFIELD IS NULL”.  Look what happens.

nulls12

Note the blank records were not selected.  This shows that blanks are not the same as NULLs.  You have to be careful of this.  If you wanted all blanks and NULLs, try this expression in the Select By Attributes tool.

nulls13

Note in this tool I had to use two single quotes with no spaces between for the SQL expression.  This selects all NULLs and blanks.

nulls14

What if I wanted to calc a NULL value to a field?  First I will select some records, like my blank values in the TFIELD, then using the Field Calculator I specify TFIELD = NULL.

nulls15

nulls16

I can do the same for the numeric field.  First I will select all records with the value of 55.

nulls17

Then using the Field Calculator I specify NFIELD = NULL.  All values of 55 are changed to NULL.

nulls18

So there you have it.  You are now an expert with dealing with NULLs and hopefully when you setup your data, you will know when to use them and NOT NULL!

7 thoughts on “ArcMap: Dealing with NULLs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s