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 in SQL Server. I need to find entries that have either null values ​​or an empty string. Thank.

Answer 1

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

or

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

Answer 2

You can simply do this:

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

Answer 3

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

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

You can replace null with your replacement value.

Answer 4

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

Answer 5

To find lines where col is NULL , an empty line or a space (spaces, tabs):

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

To find lines where col is NOT NULL , an empty line or a space (spaces, tabs):

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

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 are unacceptable ...

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

Answer 7

This is ugly MSSQL:

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

Answer 8

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

Answer 9

You can use the isnull function to get both null and empty text field values:

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

Answer 10

 --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 check '' as NULL by converting it to NULL with NULLIF

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

or you can consider 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 

Answer 11

my best solution:

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

COALESCE returns the first nonzero expr in the expression list ().

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

therefore 0 is 0, then this fieldValue is an empty or empty string.

in python for example:

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

good luck

Answer 12

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' 

Answer 13

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)

Answer 14

 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 15

You check null with IS NULL and the Empty string with LEN (RTRIM (LTRIM (Column))) = 0 in

 SELECT * FROM AppInfra.Person WHERE LEN(RTRIM(LTRIM(NationalCode))) = 0 OR NationalCode IS NULL