[sql] How to use NULL or empty string in SQL

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 is related to sql sql-server null where

The answer is


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

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

You can simply do this:

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

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'

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

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

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

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)


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

This is ugly MSSQL:

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

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

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

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


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.


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

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to null

getElementById in React Filter values only if not null using lambda in Java8 Why use Optional.of over Optional.ofNullable? How to resolve TypeError: Cannot convert undefined or null to object Check if returned value is not null and if so assign it, in one line, with one method call How do I assign a null value to a variable in PowerShell? Using COALESCE to handle NULL values in PostgreSQL How to check a Long for null in java Check if AJAX response data is empty/blank/null/undefined/0 Best way to check for "empty or null value"

Examples related to where

Get only records created today in laravel Can I do Model->where('id', ARRAY) multiple where conditions? SQLSTATE[42S22]: Column not found: 1054 Unknown column - Laravel SQL Query with Join, Count and Where PHP MySQL Query Where x = $variable How to use NULL or empty string in SQL Rails: Using greater than/less than with a where statement CASE in WHERE, SQL Server How to use "like" and "not like" in SQL MSAccess for the same field? Can the "IN" operator use LIKE-wildcards (%) in Oracle?