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

User · Answer

Just check      where value        -- not null and not empty  -- COLUMN CONTAINS A VALUE  ie string not null and not empty    --  note    lt  gt   gives a different result than   gt    select iif null     gt       true    false    -- false  null  select iif          gt       true    false    -- false  empty string  select iif          gt       true    false    -- false  space  select iif          gt       true    false    -- false  tab  select iif                       gt       true    false    -- false  newline  select iif  xxx     gt       true    false    -- true -- -- -- NOTE - test that tab and newline is processed as expected  select  x   x  -- tab select  x  x  -- newline

User · Answer

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  lt  gt       While there is a temptation to make the null check explicit     WHERE your column  lt  gt            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

User · Answer

If you only want to match    as an empty string  WHERE DATALENGTH COLUMN   gt  0    If you want to count any string consisting entirely of spaces as empty  WHERE COLUMN  lt  gt        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  lt  gt       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

User · Answer

You can use either one of these to check null  whitespace and empty strings   WHERE COLUMN  lt  gt       WHERE LEN COLUMN   gt  0  WHERE NULLIF LTRIM RTRIM COLUMN        IS NOT NULL

User · Answer

Coalesce will fold nulls into a default   COALESCE  fieldName       lt  gt

User · Answer

in basic way  SELECT   FROM  TableName  WHERE column name     AND column name IS NOT NULL

User · Answer

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   gt  8

User · Answer

An index friendly way of doing this is   where  field is not null and field  lt  gt        If there aren t many rows or this field isn t indexed  you can use    where isnull field      lt  gt

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

Examples related to sql

Examples related to sql-server