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.

Mournful

There's an OR keyword in SQL. – Inflight

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. – Taishataisho

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) – Felix

Answer 1

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

Or

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

Zest

according to @Bradc 's answer in this #800084, better to use the first approach. – Blucher

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. – Cleland

Answer 2

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.

Blucher

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

Answer 3

You can simply do this:

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

Denni

Answer 4

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

Leilaleilah

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

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

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

Answer 5

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

Herron

Answer 6

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

Bellflower

Answer 7

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

Chishima

Answer 8

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

Tramontane

Answer 9

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

Ow

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

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

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. – Breeches

Answer 10

This is ugly MSSQL:

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

Impendent

Answer 11

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

Sunglass

Answer 12

You can use in as another method

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

Antioch

Answer 13

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

Mithridate

Answer 14

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'

Kee

Answer 15

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)

Cornew

Answer 16

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

Singe

Answer 17

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

Conlin

Answer 18

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

Priestess

Answer 19

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

Timberhead

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

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? – Ruckman