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

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

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.

While checking null or Empty value for a column, I noticed that there are some support concerns in various Databases.

Every Database doesn't support TRIM method.

Below is the matrix just to understand the supported methods by different databases.

The TRIM function in SQL is used to remove specified prefix or suffix from a string. The most common pattern being removed is white spaces. This function is called differently in different databases:

• MySQL: TRIM(), RTRIM(), LTRIM()
• Oracle: RTRIM(), LTRIM()
• SQL Server: TRIM(), RTRIM(), LTRIM()

How to Check Empty/Null/Whitespace :-

Below are two different ways according to different Databases-

The syntax for these trim functions are:

1. Use of Trim to check-

   SELECT FirstName FROM UserDetails WHERE TRIM(LastName) IS NULL

2. Use of LTRIM & RTRIM to check-

   SELECT FirstName FROM UserDetails WHERE LTRIM(RTRIM(LastName)) IS NULL

Above both ways provide same result just use based on your DataBase support. It Just returns the FirstName from UserDetails table if it has an empty LastName

Hoping this will help others too :)

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 ''