sqloracle-databaseoracle10g

How to check for null/empty/whitespace values with a single test?

I'd like to write a SELECT statement that uses just one test to return columns with no value (null, empty, or all spaces).

I thought this would work:

SELECT column_name from table_name WHERE column_name NOT LIKE '%_%';

But this does not work for NULL values.

Of course I can add

OR column_name IS NULL

and it will work, but I'd like a way that uses a single test.

Solution

  • Functionally, you should be able to use

    SELECT column_name
  FROM table_name
 WHERE TRIM(column_name) IS NULL

    The problem there is that an index on COLUMN_NAME would not be used. You would need to have a function-based index on TRIM(column_name) if that is a selective condition.