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

If you only want to match “” as an empty string

WHERE DATALENGTH(COLUMN) > 0 

If you want to count any string consisting entirely of spaces as empty

WHERE COLUMN <> '' 

Both of these will not return NULL values when used in a WHERE clause. As NULL will evaluate as UNKNOWN for these rather than TRUE.

CREATE TABLE T 
  ( 
     C VARCHAR(10) 
  ); 

INSERT INTO T 
VALUES      ('A'), 
            (''),
            ('    '), 
            (NULL); 

SELECT * 
FROM   T 
WHERE  C <> ''

Returns just the single row A. I.e. The rows with NULL or an empty string or a string consisting entirely of spaces are all excluded by this query.

SQL Fiddle