LEN(NULL) != 0

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.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






LEN(NULL) != 0 | 0 comments | Create New Account
The following comments are owned by whomever posted them. This site is not responsible for what they say.


Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 
     
 Copyright © 2017 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.