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

Question

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

WHERE NULLIF(your_column, '') IS NOT NULL

---

Nowadays (4.5 years on), to make it easier for a human to read, I would just use

WHERE your_column <> ''

While there is a temptation to make the null check explicit...

WHERE your_column <> '' 
      AND your_column IS NOT NULL

...as @Martin Smith demonstrates in the accepted answer, it doesn't really add anything (and I personally shun SQL nulls entirely nowadays, so it wouldn't apply to me anyway!).

Coalesce will fold nulls into a default:

COALESCE (fieldName, '') <> ''

in basic way

SELECT *
FROM [TableName]
WHERE column_name!='' AND column_name IS NOT NULL

You can use either one of these to check null, whitespace and empty strings.

WHERE COLUMN <> '' 

WHERE LEN(COLUMN) > 0

WHERE NULLIF(LTRIM(RTRIM(COLUMN)), '') IS NOT NULL

An index friendly way of doing this is:

where (field is not null and field <> '')

If there aren't many rows or this field isn't indexed, you can use:

 where isnull(field,'') <> ''

Just check: where value > '' -- not null and not empty

-- COLUMN CONTAINS A VALUE (ie string not null and not empty) :
-- (note: "<>" gives a different result than ">")
select iif(null    > '', 'true', 'false'); -- false (null)
select iif(''      > '', 'true', 'false'); -- false (empty string)
select iif(' '     > '', 'true', 'false'); -- false (space)
select iif('    '  > '', 'true', 'false'); -- false (tab)
select iif('
'                  > '', 'true', 'false'); -- false (newline)
select iif('xxx'   > '', 'true', 'false'); -- true
--
--
-- NOTE - test that tab and newline is processed as expected:
select 'x   x' -- tab
select 'x

x' -- newline