Searching for blanks/empty is there a way?

Hi all,

A question, how do I search for blanks? These are fields that are empty. I could in the designer put in an “isnull” option to fill the blanks in but this is a real pain when simply we should be able to search for empty/blanks. Not to mention putting isnull on every column isnt a viable option

Oddly enough I can filter on blanks if I click the “phocas” button but not able to do this in the advanced search. here is an example:

If I click on Phocas in the grid:

But if I try this in advanced search:

So If I create a complex query and leave the blank search to the end (by clicking on Focus) it will work, but if I then come to edit this (in Advanced Search) I am unable to change the query anymore. Any tips out there before I put it in the feature requests?



1 Like

Hey @JonKemp ,

If I’m understanding correctly, you want to use Advanced Search to find blank values in a column, correct? I’ve had luck with doing Property → Like → * * (star space star)


That’s pulled rows with blank values in my specified column. Let me know if that works for you!


Hi @tim.janes ,

Thanks, very grateful for the tip!

This works…but the SQL developer in me makes me feel uncomfortable.

For example using “* " , in SQL land it should mean look for anything before the space and after the space, so “Google Ltd” has a space in it so I would expect that to be returned but it does not. In proper ISO SQL standards it would be “%” instead of "”, however depending on platform these symbols can be interchangable.

So in the case of Phocas does this mean the “*” is a special symbol? What is the significance of the space bar? Is it all null values are represented as " "? But if thats the case I would have expected “Google Ltd” to have been returned.

Earth calling the uber brain :wink: @Tim.Leonard , are you able to validate the significance of “* *” on null fields please, are null columns stored by default with a single space? This way my head doesnt implode with the change of what the star symbol is used for :slight_smile:

1 Like

Hi @JonKemp - I completely missed this question - my apologies.

The simplest way to find blank cells is to just type a space in the search box above the grid and hit enter. All rows that contain one or more empty property cells will be returned. To narrow down to a specific field, type the space, hit the magnifying glass and choose from the list.

You’re right - it does behave differently in advanced search. Like and Equal To will deliver different results, and @tim.janes has the right suggestion here.

  • a ‘Like’ search for * * (star space star) will return blanks (where there is no data in the cell)
  • an '‘Equal To’ search for * * (star space star) will return all data containing a space, like NOTTING HILL or MILTON KEYNES. It will not return blanks.