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.