How to Check a Column is Empty or Null in SQL Server

In SQL Server table columns, there can be times when there is NULL data or Column Value is Empty (''). When data is displayed or used in data manipulations, there could be a need to remove these records with NULL values or Empty column values or replace the NULL value with another value like EMPTY value ('') to avoid NULL value errors. There could also need to find NULL column values or Empty column data to update the NULL values or Empty values. So there needs to be some way to identify NULL and Empty column values.

In this article let us discuss in detail, how to check if a column is Empty or NULL in SQL Server, with examples and different methods.

Checking if a Column is Empty or NULL

In a Table Column sometimes there can be no actual or valid data and it could be NULL or Empty ('') or some space value saved. There are many methods or functions to identify the NULL or Empty values. Below are some of them:

1. IS NULL
   
2. IS NOT NULL
   
3. ISNULL() Function
   
4. NOT ISNULL() Function
   
5. DATALENGTH()
   
6. LEN()

Methods for Identifying NULL or Empty Values in SQL Server

Explained below with examples, the above five methods.

Below is the Table 'StudentsInfo' with data used for the examples in this article:

1. IS NULL

This key word returns any records with NULL value in the column specified in a Table.

Syntax:

WHERE {COLUMN NAME} IS NULL

Example:

Select * from StudentsInfo 
WHERE ContactPhone IS NULL

The above query checks for null values in the column 'ContactPhone' from 'StudenetsInfo' table. Below is the output.

Output:

In this example, the result includes rows where the "ContactPhone" column is NULL for various students in the "StudentsInfo" table. This query is useful for identifying records where specific contact information is missing.

2. IS NOT NULL

This key word returns any records without NULL value in the column specified.

Syntax:

WHERE {COLUMN NAME} IS NOT NULL

Example:

Select * from StudentsInfo 
WHERE ContactPhone IS NOT NULL

The above query checks for values without NULL in the column 'ContactPhone' from 'StudenetsInfo' table and returns actual values without NULL.

Output:

In the above sample output, we can see data without any NULL values, meaning records with NULL values are removed from the result output. It is to be noted that empty string value or column with spaces are displayed in the output if it is there and only NULL is filtered out. If we check the below query you can understand this as this column has EMPTY ('') and SPACES (' ')

Select * from StudentsInfo 
WHERE Remarks IS NOT NULL

Output:

3. ISNULL() Function

The function ISNULL() returns all records with 'NULL' values and records having Empty value ('') or Spaces (' ') in the specified column.

Syntax:

ISNULL(expression, replacement)

Expression can be a column name and replacement is the string value which will replace the column value.

Example:

SELECT * FROM StudentsInfo 
WHERE ISNULL(Remarks, '') = '' 

In the above example 'Select' query, all the NULL, EMPTY, and SPACE values are returned as below:

Output:

4. NOT ISNULL() Function

The function NOT ISNULL() is just the opposite of ISNULL() Function explained above and returns all records without NULL, Empty, and Spaces in a particular column specified.

Syntax:

NOT ISNULL(expression, replacement)

Example:

SELECT * FROM StudentsInfo 
WHERE NOT ISNULL(Remarks, '') = ''

The above select statement with 'NOT ISNULL()' returns all records in EmployeesInfo table which has only valid data, meaning it does not show any records with NULL or Empty column or column with Spaces.

Output:

In the above output, we can records only with valid data as explained above.

5. DATALENGTH()

The DATALENGTH() function returns the actual length of the data or spaces in the specified column. We can use the DATALENGTH() function to check for records with an Empty column.

Syntax:

WHERE DATALENGTH({Expression})

the Expression can be a column name or a string expression

Example:

Select * from StudentsInfo 
WHERE DATALENGTH(Remarks)=0

Output:

In the above output, we can see records with only EMPTY ('') value in the column 'Remarks'. Datelength returns the actual length of data including Spaces. So the Datalenth(Remarks)=0 return only the records with column having 0 length, which can be only EMPTY value.

6. LEN()

The LEN() returns the actual length of the data in specified column. But it can used to check for records with Empty column and Spaces.

Syntax:

WHERE LEN({Expression})

The Expression can be a column name or a string expression

Example:

Select * from StudentsInfo 
where LEN(Remarks) = 0 

Output:

The above Select query returns all columns with Empty ('') values and Spaces (' ') from the column Remarks in table StudentsInfo.

The above query is same as the below query:

Select * from StudentsInfo 
WHERE Remarks=''

This also returns the same result as the previous query using LEN() function.

Conclusion

In this article, we have discussed in detail about records with NULL, EMPTY(''), and SPACES (' ') values and how to filter records without these values. We have seen 6 types of keywords or Functions to filter records with or without NULL, EMPTY and SPACE values based on the query result required. Checking for NULL values is very important in many situations to avoid NULL value error. Whenever situation requires check for NULL value in a column before doing any data manipulations to avoid errors.