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

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

sqlsql-server


Solution 1:[1]

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!).

Solution 2:[2]

Coalesce will fold nulls into a default:

COALESCE (fieldName, '') <> ''

Solution 3:[3]

in basic way

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

Solution 4:[4]

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

Solution 5:[5]

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,'') <> ''

Solution 6:[6]

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

Solution 7:[7]

For some kind of reason my NULL values where of data length 8. That is why none of the abovementioned seemed to work. If you encounter the same problem, use the following code:

--Check the length of your NULL values
SELECT DATALENGTH(COLUMN) as length_column
FROM your_table

--Filter the length of your NULL values (8 is used as example)
WHERE DATALENGTH(COLUMN) > 8

Solution 8:[8]

The below statement worked for me.

$sql = "SELECT table_name FROM product_reviews WHERE title > '0'  
and  product='Product name'";

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Michael
Solution 3 Saalim Bhoraniya
Solution 4 DxTx
Solution 5 Luc
Solution 6
Solution 7 Bas de Vries
Solution 8 Abdul Shaikh

Related Questions