212

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.

poshan
  • 3,069
  • 5
  • 20
  • 30
  • 5
    There's an OR keyword in SQL. – Robert Harvey Mar 27 '13 at 15:51
  • 19
    This question does not show any research effort. It is important to **do your homework**. Tell us what you found and ***why*** it didn't meet your needs. This demonstrates that you've taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer. [FAQ](http://stackoverflow.com/questions/how-to-ask). – Kermit Mar 27 '13 at 15:53
  • 1
    possible duplicate of [How to select data from MySQL table where a column is NULL](http://stackoverflow.com/questions/14279947/how-to-select-data-from-mysql-table-where-a-column-is-null) (this was a MySQL question, but it's basically the same answer for t-sql) – Michael Berkowski Mar 27 '13 at 15:53

17 Answers17

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

Or

Select *
From Table
Where IsNull(col, '') = ''
codingbadger
  • 42,678
  • 13
  • 95
  • 110
  • 20
    according to @Bradc 's answer in this http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable?lq=1, better to use the first approach. – Sameera R. Feb 09 '16 at 05:30
  • 1
    Also note that the comments say that In MSSQL 2016, there is no execution plan difference between using ISNULL in WHERE as opposed to using OR in WHERE. It's said that they both use the index on 'col' and do an index seek. – Ε Г И І И О Aug 03 '21 at 08:14
62

If you need it in SELECT section can use like this.

SELECT ct.ID, 
       ISNULL(NULLIF(ct.LaunchDate, ''), null) [LaunchDate]
FROM   [dbo].[CustomerTable] ct

You can replace the null with your substitution value.

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
Sameera R.
  • 4,384
  • 2
  • 36
  • 53
  • 2
    Oracle's NVL(var1, 'value') will take care of empty string ' ' replacement. T-SQL's Isnull(var1, 'value') doesn't. – Jenna Leaf Oct 14 '19 at 21:31
30

You can simply do this:

SELECT *
FROM   yourTable
WHERE  yourColumn IS NULL OR yourColumn = ''
Praveen Nambiar
  • 4,852
  • 1
  • 22
  • 31
25
SELECT *
FROM   TableName
WHERE  columnNAme IS NULL OR 
       LTRIM(RTRIM(columnName)) = ''
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • 4
    SELECT * FROM TableName WHERE columnNAme IS NULL OR RTRIM(columnName) = '' – Xavier John Apr 14 '15 at 18:52
  • 3
    As Xavier points out, there is no need to do BOTH `ltrim` and `rtrim` if the goal is merely to compare to empty string. REASON: if there is ONLY whitespace, a single trim will remove all of it. For example, you don't care whether the comparison fails because what remains is "abc" or " abc". – ToolmakerSteve Dec 09 '16 at 20:06
  • Technically the `trim` functions remove _spaces_, not _whitespace_ — at least, so says the documentation. – Auspex Jan 21 '22 at 11:42
8

To find rows where col is NULL, empty string or whitespace (spaces, tabs):

SELECT *
FROM table
WHERE ISNULL(LTRIM(RTRIM(col)),'')=''

To find rows where col is NOT NULL, empty string or whitespace (spaces, tabs):

SELECT *
FROM table
WHERE ISNULL(LTRIM(RTRIM(col)),'')<>''
Enamul Hassan
  • 5,266
  • 23
  • 39
  • 56
Scott Sterling
  • 81
  • 1
  • 2
8

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,'') = '';
Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
4

my best solution : 

 WHERE  
 COALESCE(char_length(fieldValue), 0) = 0

COALESCE returns the first non-null expr in the expression list().

if the fieldValue is null or empty string then: we will return the second element then 0.

so 0 is equal to 0 then this fieldValue is a null or empty string.

in python for exemple:

def coalesce(fieldValue):
    if fieldValue in (null,''):
        return 0

good luck

Zaheer Ahmed
  • 28,160
  • 11
  • 74
  • 110
Anouar Mokhtari
  • 2,084
  • 4
  • 23
  • 23
3

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,'') = ''
Alberto De Caro
  • 5,147
  • 9
  • 47
  • 73
  • The isnull function literally just checks if the value is null. It will not work if the string is empty(" "). – Max Pringle Mar 05 '18 at 16:44
  • 1
    @Max Pringle. I do not state differently. Also the provided code address the question request. Not sure on your contribution. Suggest an edit instead of downvoting. – Alberto De Caro Mar 05 '18 at 16:52
  • 1
    will take your advice. I put in a suggested edit now. This where clause only gets the null values not the empty string values. A nullif could be used to get the empty values. – Max Pringle Mar 05 '18 at 16:59
3

This is ugly MSSQL:

CASE WHEN LTRIM(RTRIM(ISNULL([Address1], ''))) <> '' THEN [Address2] ELSE '' END
ADM-IT
  • 3,719
  • 1
  • 25
  • 26
2
SELECT *
FROM   Table
WHERE  column like '' or column IS NULL OR LEN(column) = 0
Charaf JRA
  • 8,249
  • 1
  • 34
  • 44
2
select 
   isnull(column,'') column, * 
from Table  
Where column = ''
chandu komati
  • 795
  • 1
  • 5
  • 21
2

You can use in as another method

Select *
From Table
Where column in(null, '')
Yasin UYSAL
  • 571
  • 6
  • 11
1
--setup
IF OBJECT_ID('tempdb..#T') IS NOT NULL DROP TABLE #T;
CREATE TABLE #T(ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, NAME VARCHAR(10))
INSERT INTO #T (Name) VALUES('JOHN'),(''),(NULL);
SELECT * FROM #T
 1  JOHN
 2  -- is empty string
 3  NULL

You can examine '' as NULL by converting it to NULL using NULLIF

--here you set '' to null
UPDATE #T SET NAME = NULLIF(NAME,'')
SELECT * FROM #T 
 1  JOHN
 2  NULL
 3  NULL

or you can examine NULL as '' using SELECT ISNULL(NULL,'')

-- here you set NULL to ''
UPDATE #T SET NAME = ISNULL(NULL,'') WHERE NAME IS NULL
SELECT * FROM #T
1   JOHN
2   -- is empty string
3   -- is empty string

--clean up
DROP TABLE #T
Biniam Eyakem
  • 545
  • 5
  • 18
1

In sproc, you can use the following condition:

DECLARE @USER_ID VARCAHR(15)=NULL --THIS VALUE IS NULL OR EMPTY DON'T MATTER
IF(COALESCE(@USER_ID,'')='')
PRINT 'HUSSAM'
Pang
  • 9,564
  • 146
  • 81
  • 122
mohammed hussamuddin hussamudd
  • 25
  • 2
1

by this function:

ALTER FUNCTION [dbo].[isnull](@input nvarchar(50),@ret int = 0)
RETURNS int
AS
BEGIN

    return (case when @input='' then @ret when @input is null then @ret else @input end)

END

and use this:

dbo.isnull(value,0)

Mohsen.Sharify
  • 259
  • 4
  • 11
1
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()
Vinicius.Beloni
  • 141
  • 4
1

youe check null With IS NULL and string Empty With LEN(RTRIM(LTRIM(Column))) = 0 in 

SELECT *
FROM AppInfra.Person
WHERE   LEN(RTRIM(LTRIM(NationalCode))) = 0 OR  NationalCode IS NULL
Alireza Shabani
  • 64
  • 1
  • 8