Welcome to Julian Kuiters Saturday, August 18 2018 @ 05:11 PM AEST

LEN(NULL) != 0

  • Contributed by:
Database Design

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.