How to use NULL or empty string in SQL

Question

I would like to know how to use NULL and an empty string at the same time in a WHERE clause in SQL Server. I need to find records that have either null values or an empty string. Thanks.

Answer 1

You could use isnull function to get both null and empty values of a text field:

SELECT * FROM myTable
WHERE isnull(my_nullable_text_field,'') = ''

Answer 2

SELECT * FROM DBO.AGENDA
WHERE 
  --IF @DT_START IS NULL OR EMPTY
  ( ISNULL( @DT_START,'' ) = '' AND DT_START IS NOT NULL ) -- GET ALL DATE
  OR --ELSE
  ( DT_START >= @DT_START ) --FILTER

-- MORE FILTER

SELECT * FROM DBO.AGENDA
WHERE 
  ( ( ISNULL( @DT_START,'' ) = '' AND DT_START IS NOT NULL ) OR ( DT_START >= @DT_START ) ) 
  AND
  DT_END < GETDATE()

Answer 3

Select *
From Table
Where (col is null or col = '')

Or

Select *
From Table
Where IsNull(col, '') = ''

Answer 4

SELECT *
FROM   Table
WHERE  column like '' or column IS NULL OR LEN(column) = 0

Answer 5

Some sargable methods...

SELECT *
FROM #T
WHERE SomeCol = '' OR SomeCol IS NULL;

SELECT *
FROM #T
WHERE SomeCol = '' 
UNION ALL
SELECT *
FROM #T
WHERE  SomeCol IS NULL;

SELECT *
FROM #T
WHERE EXISTS ((SELECT NULL UNION SELECT '') INTERSECT SELECT SomeCol);

And some non-sargable ones...

SELECT *
FROM #T
WHERE IIF(SomeCol <> '',0,1) = 1;

SELECT *
FROM #T
WHERE NULLIF(SomeCol,'') IS NULL;

SELECT *
FROM #T
WHERE ISNULL(SomeCol,'') = '';

Answer 6

You can simply do this:

SELECT *
FROM   yourTable
WHERE  yourColumn IS NULL OR yourColumn = ''