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

Question

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.

Answer 1

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.

Answer 2

SELECT column_name from table_name
WHERE RTRIM(ISNULL(column_name, '')) LIKE ''

ISNULL(column_name, '') will return '' if column_name is NULL, otherwise it will return column_name.

UPDATE

In Oracle, you can use NVL to achieve the same results.

SELECT column_name from table_name
WHERE RTRIM(NVL(column_name, '')) LIKE ''

Answer 3

The NULLIF function will convert any column value with only whitespace into a NULL value. Works for T-SQL and SQL Server 2008 & up.

SELECT [column_name]
FROM [table_name]
WHERE NULLIF([column_name], '') IS NULL