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.

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

You can simply do this:

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

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

Or

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

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

You could use [`isnull`](

[To see links please register here]

) function to get both `null` and empty values of a text field:

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

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

This is ugly MSSQL:

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

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


[1]:

[To see links please register here]