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

Answer 4

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 :)

Answer 5

This phpMyAdmin query is returning those rows, that are NOT null or empty or just whitespaces:

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

if you want to select rows that are null/empty/just whitespaces just remove NOT.

Answer 6

Although @MerrickPlainview answer seems close and small, the full answer (to also deal with white space as the OP asked for) would be this:

SELECT column_name FROM table_name WHERE NULLIF(TRIM(column_name), '') IS NOT NULL

Answer 7

Use below query and it works

SELECT column_name FROM table_name where isnull(column_name,'') <> ''

Answer 8

What I use for IsNotNullOrEmptyOrWhiteSpace in T-SQL is:

SELECT [column_name] FROM [table_name]
WHERE LEN(RTRIM(ISNULL([column_name], ''))) > 0

Answer 9

This covers null and empty spaces

TRIM(ColumnName) <> ''

Answer 10

You can also check this. This works for me, when you want to fetch both null value and another condition (i.e) my query should return the rows where columnname should be null and should have the word 'questiontext'.

SELECT * 
FROM `table_name` 
WHERE (('columnname' IS NULL) OR (TRIM('columnname') LIKE 'questiontext'))

Here, my 'columnname' should be equal to 'questiontext'.

Answer 11

select * from table_name where column_name NOT Like '%_%' or column_name is null

Answer 12

None of the suggestions worked for me. Here is what finally worked for me:

I am not sure why it would not work with < 1, but for some reason < 2 worked and only returned records whose field is just blank.

select [columnName] from [tableName] where LENGTH(columnName) < 2 ;

I am guessing whatever script that was used to convert data over has left something in the field even though it shows blank, that is my guess anyways as to why the < 2 works but not < 1.

However, if you have any other values in that column field that are less than two characters then you might have to come up with another solution. If there are not a lot of other characters then you can single them out.

Answer 13

As in Oracle you can use NVL function in MySQL you can use IFNULL(columnaName, newValue) to achieve your desired result as in this example

SELECT column_name from table_name WHERE IFNULL(column_name,'') NOT LIKE '%_%';

Answer 14

you can use

SELECT [column_name] 
FROM [table_name]
WHERE [column_name] LIKE '% %' 
OR [column_name] IS NULL