How to check for Is not Null And Is not Empty string in SQL server?

Question

How can we check in a SQL Server WHERE condition whether the column is not null and not the empty string ('')?

Answer 1

 1: If you just want to match " " as an empty string, then use the WHERE clause like this:

WHERE DATALENGTH(COLUMN) > 0 

2: But, if you want to count any string which consists of entire spaces as empty then use the WHERE clause like this :

WHERE COLUMN <> '' 

The above code will not return NULL values when they are used in a WHERE clause. And NULL will evaluate as UNKNOWN for these rather than the TRUE value.

CREATE TABLE T 

  ( 

     C VARCHAR(10) 

  ); 

INSERT INTO T 

VALUES      ('A'), 

            (''),

            ('    '), 

            (NULL); 

SELECT * 

FROM   T 

WHERE  C <> ''

The above code will return only the single row A. The rows that have the NULL or an empty string or a string consisting entirely of spaces are all excluded by this query.

Visit this SQL server certification to learn SQL Full Package.

Answer 2

Select is used to retrieve data

“ * “ is used to get the data of all columns

‘From tablename’ is used to retrieve from table name

‘Where’ is a clause used to check the condition

‘Is not null’ is used for checking for the condition that there is any null or not

‘ != ‘’ ’ is used to check where it is an empty string, which is given by user

Answer 3

In order to determine if a column in SQL Server is not NULL and not an empty string, we can employ a WHERE clause that consists of two conditions. This is achieved by making sure that the column values are not NULL and are not an empty string as well.

If you want to implement follow this:

SELECT *

FROM your_table

WHERE your_column IS NOT NULL 

  AND your_column <> '';