How do you check if a column is blank or Null in SQL?

In this article, you going to see how to check column blank or Null in SQL in detail and step by step with the code snippet.

If you are looking for this topic then you are a right place.

Let’s start with topic regarding how to check if a column is blank or Null in SQL.

SQL NULL Values is a field with a NULL value is a field with no value.

Below is the syntax IS NULL.

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

Below is the syntax for IS NOT NULL.

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

Now the point is how to check this for NULL Values

(e.g. null or '' or ' ' or ' ' and …)

Below is code, which will select all rows where some_col is NULL or '' (empty string).

SELECT * FROM table WHERE some_col IS NULL OR some_col = '';

If you change the condition to WHERE some_col IS NULL OR some_col = ' ' (one space inserted in the string) then it works on both MySQL and Oracle.

some_col = '' doesn’t work on Oracle as empty strings mean NULL.

According to defined by the SQL-92 Standard, when comparing between 2 strings of differing widths, the narrower value is right-padded with spaces to make it is same width as the wider value.

Therefore, all string values that consist entirely of spaces (including zero spaces) will be deemed to be equal e.g.

'' = ' ' IS TRUE
'' = '  ' IS TRUE
' ' = '  ' IS TRUE
'  ' = '      ' IS TRUE
etc

Therefore, this should work regardless of how many spaces make up the some_col value:

SELECT * 
  FROM T
 WHERE some_col IS NULL 
       OR some_col = ' ';

or more concisely:

SELECT * 
  FROM T
 WHERE NULLIF(some_col, ' ') IS NULL;

So when you change the condition to WHERE some_col IS NULL OR some_col = ' ' (one space inserted in the string) then it works on both MySQL and Oracle. some_col = '' doesn’t work on Oracle as empty strings mean NULL there and the complete condition becomes NULL.

SQL Trigger – Employee Database

Conclusion :

Finally, from the above different code you understand regarding how do you check if a column is blank or Null in SQL.

I hope you like this article and if you have any query, please comment below.