Its pretty often that I run across SQL code that supposed to handle empty or
null varchar values. But a lot of the time it doesn’t. And usually its because a
developer has made the false assumption that null has a length of 0. It doesn’t.

LEN(NULL) IS NULL.

LEN(NULL) does not = anything.
LEN(NULL) does not != anything.

If you want to handle rows where there is a possibility of the value being
null or an empty string, you have to handle both conditions:

WHERE column IS NULL or LEN(column) = 0

Here’s an example to showing you LEN(column) = 0 wont return null rows.

-- Create a table with nullable varchar column

CREATE TABLE dbo.lentest (

col1 VARCHAR(32) NULL,

col2 VARCHAR(128) NULL);

-- Insert some mixed value rows

INSERT INTO dbo.lentest
(col1,
col2)
VALUES ('Some text',
'A String of Text');

INSERT INTO dbo.lentest
(col1,
col2)
VALUES ('', 'An empty String');

INSERT INTO dbo.lentest
(col1,
col2)
VALUES (NULL,
'Row with a null Value');

-- Notice the Col1Length values ?

-- LEN(NULL) is actually NULL

SELECT col1,

Len(col1) AS col1length,

col2

FROM dbo.lentest;

-- This only returns one row

SELECT col1,

Len(col1) AS col1length,

col2

FROM dbo.lentest

WHERE Len(col1) = 0;

-- and the negative logic also returns only one row

SELECT col1,

Len(col1) AS col1length,

col2

FROM dbo.lentest

WHERE Len(col1) != 0;

-- hey! what happened to that third row!?

SELECT col1,

Len(col1) AS col1length,

col2

FROM dbo.lentest

WHERE Len(col1) IS NULL ;

-- the above is really the same as saying

SELECT col1,

Len(col1) AS col1length,

col2

FROM dbo.lentest

WHERE col1 IS NULL ;

-- so to get all the rows that have a null or empty value

SELECT col1,

Len(col1) AS col1length,

col2

FROM dbo.lentest

WHERE col1 IS NULL OR Len(col1) = 0;

You can
download this sample
.