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.

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. — Kermit
– Kermit, Commented Mar 27, 2013 at 15:53
possible duplicate of 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
– Michael Berkowski, Commented Mar 27, 2013 at 15:53

codingbadger · Accepted Answer · 2013-03-27 15:55:01Z

378

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

Or

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

answered Mar 27, 2013 at 15:55

codingbadger

44.3k13 gold badges99 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sameera R. Over a year ago

according to @Bradc 's answer in this stackoverflow.com/questions/799584/…, better to use the first approach.

Ε Г И І И О Over a year ago

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.

Uwe Keim · Accepted Answer · 2021-08-03 05:49:03Z

71

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.

edited Aug 3, 2021 at 5:49

Uwe Keim

40.9k61 gold badges193 silver badges308 bronze badges

answered Dec 22, 2015 at 10:40

Sameera R.

4,6402 gold badges39 silver badges53 bronze badges

1 Comment

Jenna Leaf Over a year ago

Oracle's NVL(var1, 'value') will take care of empty string ' ' replacement. T-SQL's Isnull(var1, 'value') doesn't.

Praveen Nambiar · Accepted Answer · 2013-03-27 15:54:37Z

31

You can simply do this:

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

answered Mar 27, 2013 at 15:54

Praveen Nambiar

4,9022 gold badges24 silver badges32 bronze badges

Comments

John Woo · Accepted Answer · 2013-03-27 15:53:09Z

27

SELECT *
FROM   TableName
WHERE  columnNAme IS NULL OR 
       LTRIM(RTRIM(columnName)) = ''

answered Mar 27, 2013 at 15:53

John Woo

265k70 gold badges509 silver badges500 bronze badges

3 Comments

Xavier John Over a year ago

SELECT * FROM TableName WHERE columnNAme IS NULL OR RTRIM(columnName) = ''

ToolmakerSteve Over a year ago

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".

Auspex Over a year ago

Technically the trim functions remove spaces, not whitespace — at least, so says the documentation.

Enamul Hassan · Accepted Answer · 2015-10-09 03:32:18Z

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

edited Oct 9, 2015 at 3:32

Enamul Hassan

5,47323 gold badges45 silver badges61 bronze badges

answered Oct 9, 2015 at 2:30

Scott Sterling

811 silver badge2 bronze badges

1 Comment

Developer Webs Over a year ago

You don't need to ltrim or rtrim when using <> ''. This condition will also exclude values that are whitespace only.

Community · Accepted Answer · 2017-05-23 11:54:44Z

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

edited May 23, 2017 at 11:54

CommunityBot

11 silver badge

answered Dec 8, 2015 at 18:17

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Comments

Zaheer Ahmed · Accepted Answer · 2019-06-13 08:01:23Z

5

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

edited Jun 13, 2019 at 8:01

Zaheer Ahmed

28.7k12 gold badges77 silver badges113 bronze badges

answered Feb 15, 2017 at 13:47

Anouar Mokhtari

2,1935 gold badges23 silver badges23 bronze badges

Comments

Charaf JRA · Accepted Answer · 2013-03-27 15:55:19Z

3

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

answered Mar 27, 2013 at 15:55

Charaf JRA

8,3551 gold badge36 silver badges44 bronze badges

Comments

Alberto De Caro · Accepted Answer · 2013-03-27 15:59:08Z

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

answered Mar 27, 2013 at 15:59

Alberto De Caro

5,2139 gold badges50 silver badges77 bronze badges

3 Comments

Max Pringle Over a year ago

The isnull function literally just checks if the value is null. It will not work if the string is empty(" ").

Alberto De Caro Over a year ago

@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.

Max Pringle Over a year ago

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.

ADM-IT · Accepted Answer · 2015-09-29 16:37:24Z

3

This is ugly MSSQL:

CASE WHEN LTRIM(RTRIM(ISNULL([Address1], ''))) <> '' THEN [Address2] ELSE '' END

answered Sep 29, 2015 at 16:37

ADM-IT

4,2901 gold badge31 silver badges30 bronze badges

Comments

Chandrashekhar Komati · Accepted Answer · 2020-06-03 08:58:41Z

2

select 
   isnull(column,'') column, * 
from Table  
Where column = ''

answered Jun 3, 2020 at 8:58

Chandrashekhar Komati

7941 gold badge5 silver badges21 bronze badges

Comments

Yasin UYSAL · Accepted Answer · 2022-04-30 18:44:52Z

2

You can use in as another method

Select *
From Table
Where column in(null, '')

answered Apr 30, 2022 at 18:44

Yasin UYSAL

6198 silver badges12 bronze badges

Comments

Biniam Eyakem · Accepted Answer · 2014-11-07 14:23:17Z

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

answered Nov 7, 2014 at 14:23

Biniam Eyakem

5546 silver badges21 bronze badges

Comments

Pang · Accepted Answer · 2017-05-11 08:24:05Z

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'

edited May 11, 2017 at 8:24

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered May 11, 2017 at 7:59

mohammed hussamuddin hussamudd

192 bronze badges

Comments

Mohsen.Sharify · Accepted Answer · 2017-08-05 11:32:15Z

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)

answered Aug 5, 2017 at 11:32

Mohsen.Sharify

2574 silver badges11 bronze badges

Comments

Vinicius.Beloni · Accepted Answer · 2018-01-18 20:06:49Z

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

answered Jan 18, 2018 at 20:06

Vinicius.Beloni

1515 bronze badges

Comments

Alireza Shabani · Accepted Answer · 2018-08-13 12:40:59Z

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

answered Aug 13, 2018 at 12:40

Alireza Shabani

641 silver badge9 bronze badges

Comments

Yitzhak Weinberg · Accepted Answer · 2024-01-30 12:38:44Z

1

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

SELECT COALESCE(column1, column2) AS columnName
FROM tabel;

if column1 is null column2 will be return

answered Jan 30, 2024 at 12:38

Yitzhak Weinberg

2,6221 gold badge22 silver badges22 bronze badges

Comments

Neha · Accepted Answer · 2024-01-31 04:33:54Z

1

if you want to fetch empty and null values from one column then this code apply

select columnname2 from tablename
where columnname2 is null or columnname2 = '';

select columnlist from table you want to fetch then where in which column you want to fetch values of null then write columnname is null and also you want to find empty values then write perticular column name then = '' this will show that empty values from perticular column

edited Jan 31, 2024 at 4:33

answered Jan 30, 2024 at 13:14

Neha

113 bronze badges

2 Comments

Neha Over a year ago

select columnname from tablename where columnname2 is null or columnname2 = '';

Jeremy Caney Over a year ago

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community?

Collectives™ on Stack Overflow

How to use NULL or empty string in SQL

19 Answers 19

2 Comments

1 Comment

Comments

3 Comments

1 Comment

Comments

Comments

Comments

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

19 Answers 19

2 Comments

1 Comment

Comments

3 Comments

1 Comment

Comments

Comments

Comments

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related