NULL value not equal to empty value for attribute

Question

I have a feature layer in a gdb. It has an attribute named uniqueid. Some of rows have a value for uniqueid and some do not. I am trying to select only the features that have a value (are not blank).

enter image description here

Using the Select by Attributes dialog box, I am trying to create an expression that will weed out the blank features. I tried using where: unique IS NOT NULL but that selected everything, both the features with a value and those that were blank. I then tried using Get Unique Values and chose '', which was one of the options. It through an error. What am I doing wrong here? How can I select just the features that have a value for the uniqueid attribute field?

enter image description here

Here's the actual text version of the error as requested:

There was a problem selecting

An invalid SQL statement was used. An invalid SQL statement was used.[fottprint_cslf_union_20181004] An invalid SQL statement was used.[SELECT OBJECTID FROM footprint_cslf_union_20181004 WHERE uniqueid IS NOT '']

• EranGeo: Have you tried uniqueid <> '' ?
• jbalk: Can you post the error when you try uniquid <> ''. Make sure it's 2 single quotes, not a double quote.
• Vince: Please Edit the question to specify the error in ASCII text this makes the error message legible on all devices and permits free text search. I'm not aware of any language that would accept *column* IS NOT ''. Part of your issue may be that UNIQUE is a reserved word in SQL, so there may be an alias to an actual column name unique_.
• Jelle: Another alternative you could try is to create a new column and use the ‘Field Calculator’ to write a python script that reclasses the values based on the ‘uniqueid’ column, as shown here: gis.stackexchange.com/questions/266620/… . Afterwards you can ‘select by Attribute’ through your new column.
• Keagan Allan: What if you used a "Select by Attributes" similar to "uniqueid LIKE '%CA%'" or "uniqueid LIKE '%0%'"

Answer 1

The IS NOT syntax can only be used with null but you have a '' (an empty string value delimited by quote) so the correct syntax should be uniqueid <> ''

I think this is because your field is a text field where null/none/empty value are stored as empty string (thus the '' in the value selector), in a numeric field you get "real" null value and could use the uniqueid IS NOT null syntax

• gwydion93: OK, this turned out to be the correct answer. For whatever reason, uniqueid <> '' did not work originally. Maybe I was adding a space in the quotes. Whatever the reason, I shot down arcMap and restarted and it magically worked. I popped the same statement in a arcpy.selectByAttribute_management(...) function and it worked. Thanks.

Answer 2

You can try to use the Wildcard "%" to Select By Attributes.

By using the Wildcard to form a statement that should contain characters that all the attributes should have you can select the Attributes that are not ''.

The syntax would be:

uniqueid LIKE '%CA%'

Or if CA is not common to all the attributes, try a numerical attribute value:

uniqueid LIKE '%0%'

You may need to string a few of these together to cover all your bases / potential combinations:

uniqueid LIKE '%0%' OR uniqueid LIKE '%1%' OR uniqueid LIKE '%CA%'

Depending on the values in the uniqueid field.