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