/*
 Julian Kuiters 2006
 http://www.julian-kuiters.id.au

 Sample code from article: "LEN(NULL) != 0"
*/

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