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.

3

17 Answers

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

Or

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

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.

1

You can simply do this:

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

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

3

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

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

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

0

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

3

This is ugly MSSQL:

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

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

0

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

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

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'

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)

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

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

You can use in as another method

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

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy