CODEWITHSUNDEEP
sqlsql-server
user993935
user993935

Reputation: 3903

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 ('')?

Upvotes: 375

Views: 1032354

Answers (10)

chandra wijaksana
chandra wijaksana

Reputation: 31

Check the not null condition and empty string in SQL command is use 'is null / not null' and '!='. please try this sample pattern script:

SELECT * FROM [Employee] 
WHERE
    EMail is not null -- not null check
    and Email != '' -- not empty check

Upvotes: 2

Rameesh Puthukkudy
Rameesh Puthukkudy

Reputation: 127

check this way where Ph != '' and Ph = '123456780'

Upvotes: -1

Bas de Vries
Bas de Vries

Reputation: 69

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

Upvotes: -1

DxTx
DxTx

Reputation: 3377

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

Upvotes: 23

Saalim Bhoraniya
Saalim Bhoraniya

Reputation: 360

in basic way

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

Upvotes: 32

Ellis
Ellis

Reputation: 429

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

Upvotes: 2

onedaywhen
onedaywhen

Reputation: 57073

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

Upvotes: 195

Luc
Luc

Reputation: 1491

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

Upvotes: 9

Martin Smith
Martin Smith

Reputation: 453807

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

Upvotes: 498

Anoop Verma
Anoop Verma

Reputation: 1505

Coalesce will fold nulls into a default:

COALESCE (fieldName, '') <> ''

Upvotes: 20

Related Questions