Coder Perfect

How can I use a single test to check for null, empty, or whitespace values?

Problem

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

This is what I believed would work:

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

This, however, does not function with NULL values.

Of course, I can contribute something.

OR column_name IS NULL

It will work, but I’d prefer a method that relies on a single test.

Asked by John Gordon

Solution #1

You should be able to use it in terms of functionality.

SELECT column_name
  FROM table_name
 WHERE TRIM(column_name) IS NULL

The issue is that an index on COLUMN NAME would not be used in this case. If TRIM(column name) is a selective condition, you’ll need to use a function-based index.

Answered by Justin Cave

Solution #2

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

If column name is NULL, ISNULL(column name, “) returns “; otherwise, column name is returned.

UPDATE

You may get the same effects in Oracle by using NVL.

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

Answered by GendoIkari

Solution #3

Any column value containing only whitespace will be converted to a NULL value using the NULLIF function. T-SQL and SQL Server 2008 and later are supported.

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

Answered by MerrickPlainview

Solution #4

I discovered certain support problems in various Databases while checking null or empty values for a column.

The TRIM technique is not supported by all databases.

The matrix below is merely to help you understand which methods are supported by which databases.

In SQL, the TRIM function is used to delete a string’s specified prefix or suffix. White spaces are the most commonly eliminated pattern. In different databases, this function is referred to as:

Checking for Empty/Null/Whitespace:-

These trim functions have the following syntax:

Both methods yield the same result; choose one dependent on your DataBase support. If the UserDetails table has an empty LastName, it just returns the FirstName.

I’m hoping this will be useful to others as well:)

Answered by Vikash Pandey

Solution #5

This phpMyAdmin query returns all rows that aren’t null, empty, or contain only whitespaces:

SELECT * FROM `table_name` WHERE NOT ((`column_name` IS NULL) OR (TRIM(`column_name`) LIKE ''))

Remove NOT if you wish to select rows that are null/empty/just whitespaces.

Answered by Chose

Post is based on https://stackoverflow.com/questions/4109435/how-to-check-for-null-empty-whitespace-values-with-a-single-test