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